How to query for rows that have highest column value among rows that have same value for one of the columns
16,520
Solution 1
Another solution that would work on SQL Server 2000 (same as INNER JOIN above, but slightly faster) is:
SELECT id, userId, Score
FROM UserScores
WHERE id in (SELECT MAX(id)
FROM UserScores
GROUP BY userId
)
ORDER BY userId
Solution 2
How about
SELECT MAX(Id), userId, Score
FROM table
GROUP BY UserId
Solution 3
Use:
WITH summary AS (
SELECT t.id,
t.userid,
t.score,
ROW_NUMBER() OVER (PARTITION BY t.userid ORDER BY t.id DESC, t.score DESC) AS rank
FROM USERSCORES sc)
SELECT s.id,
s.userid,
s.score
FROM summary s
WHERE s.rank = 1
Solution 4
SELECT U2.id, U2.userId, U2.score
FROM UserScores U2
INNER JOIN (
SELECT U1.userId, MAX(U1.Id) MaxId
FROM UserScores U1
GROUP BY U1.userId
) U3
ON U2.id = U3.MaxId and U2.userId = U3.userId
ORDER BY U2.userId
Author by
vnkotak
Updated on June 04, 2022Comments
-
vnkotak about 2 years
I have UserScores Table with data like this:
Id userId Score 1 1 10 2 2 5 3 1 5
I would like to have a query or SQL block that can give me the following output
Id userId Score 3 1 5 2 2 5
That is, I would like to pick rows that are unique by 'user id' that belonging to the highest 'id' column value.