# 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

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
```
User contributions licensed under: CC BY-SA
8 People found this is helpful