Get row for each unique user based on highest column value

I have the following data

+--------+-----------+--------+
| UserId | Timestamp | Rating |
+--------+-----------+--------+
|      1 |         1 |   1202 |
|      2 |         1 |   1198 |
|      1 |         2 |   1204 |
|      2 |         2 |   1196 |
|      1 |         3 |   1206 |
|      2 |         3 |   1194 |
|      1 |         4 |   1198 |
|      2 |         4 |   1202 |
+--------+-----------+--------+

I am trying to find the distribution of each user’s Rating, based on their latest row in the table (latest is determined by Timestamp). On the path to that, I am trying to get a list of user IDs and Ratings which would look like the following

+--------+--------+
| UserId | Rating |
+--------+--------+
|      1 |   1198 |
|      2 |   1202 |
+--------+--------+

Trying to get here, I sorted the list on UserId and Timestamp (desc) which gives the following.

+--------+-----------+--------+
| UserId | Timestamp | Rating |
+--------+-----------+--------+
|      1 |         4 |   1198 |
|      2 |         4 |   1202 |
|      1 |         3 |   1206 |
|      2 |         3 |   1194 |
|      1 |         2 |   1204 |
|      2 |         2 |   1196 |
|      1 |         1 |   1202 |
|      2 |         1 |   1198 |
+--------+-----------+--------+

So now I just need to take the top N rows, where N is the number of players. But, I can’t do a LIMIT statement as that needs a constant expression, as I want to use count(id) as the input for LIMIT which doesn’t seem to work.

Any suggestions on how I can get the data I need?

Cheers! Andy

Answer

This should work:

 SELECT test.UserId, Rating FROM test 
 JOIN
    (select UserId, MAX(Timestamp) Timestamp FROM test GROUP BY UserId) m
 ON test.UserId = m.UserId AND test.Timestamp = m.Timestamp

If you can use WINDOW FUNCTIONS then you can use the following:

SELECT UserId, Rating FROM(
    SELECT UserId, Rating, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Timestamp DESC) row_num FROM test 
 )m WHERE row_num = 1

Advertisement

Answer