CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Krishna Kumar
Krishna Kumar

Reputation: 8201

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

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.

Upvotes: 4

Views: 16546

Answers (4)

Niikola
Niikola

Reputation: 1462

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

Upvotes: 3

Mafu Josh
Mafu Josh

Reputation: 2672

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

Upvotes: 1

Marc B
Marc B

Reputation: 360612

How about

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

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332581

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

Upvotes: 2

Related Questions