Not entirely sure what the problem is with your query/why you are getting incorrect data, i mocked up your data set and the query provides expected results.
DECLARE @CpMaxValueRow TABLE (
CustomerId INT NULL,
EndWeight FLOAT NULL
);
INSERT INTO @CpMaxValueRow
( CustomerId, EndWeight )
VALUES ( 1,
0.5
)
INSERT INTO @CpMaxValueRow
( CustomerId, EndWeight )
VALUES ( 1,
1
)
INSERT INTO @CpMaxValueRow
( CustomerId, EndWeight )
VALUES ( 2,
.5
)
INSERT INTO @CpMaxValueRow
( CustomerId, EndWeight )
VALUES ( 2,
1
)
INSERT INTO @CpMaxValueRow
( CustomerId, EndWeight )
VALUES ( 2,
1
)
SELECT * FROM @CpMaxValueRow AS A WHERE CustomerId = 1 AND EndWeight = (SELECT MAX(B.EndWeight) FROM @CpMaxValueRow AS B)