How to query rows that have the largest column value among rows that have the same value for one of the columns

Question

I have a 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 select rows that are unique in "user id" that refer to the highest value of the "id" column.

Answer 1

Another solution that will run on SQL Server 2000 (same as INNER JOIN above, but slightly faster):

 SELECT id, userId, Score FROM UserScores WHERE id in (SELECT MAX(id) FROM UserScores GROUP BY userId ) ORDER BY userId 

Answer 2

Using:

 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 

Answer 3

What about

 SELECT MAX(Id), userId, Score FROM table GROUP BY UserId 

Answer 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