Olanrewaju Lax Lawal Olanrewaju Lax Lawal - 15 days ago 7
MySQL Question

Get user's highest score from a table

I have a feeling this is a very simple question but maybe i'm having brain fart right now and just can't seem to figure out how to go about it.

I have a MySQL table structure like below

+---------------------------------------------------+
| id | date | score | speed | user_id |
+---------------------------------------------------+
| 1 | 2016-11-17 | 2 | 133291 | 17 |
| 2 | 2016-11-17 | 6 | 82247 | 17 |
| 3 | 2016-11-17 | 6 | 21852 | 17 |
| 4 | 2016-11-17 | 1 | 109338 | 17 |
| 5 | 2016-11-17 | 7 | 64762 | 61 |
| 6 | 2016-11-17 | 8 | 49434 | 61 |


Now i can get a particular user's best performance by doing this

SELECT *
FROM performance
WHERE user_id = 17 AND date = '2016-11-17'
ORDER BY score desc,speed asc LIMIT 1


This should return the row with ID = 3. Now what I want is a single query to run to be able to return that 1 such row for each unique user_id in the table. So the resulting result would be something like this

+---------------------------------------------------+
| id | date | score | speed | user_id |
+---------------------------------------------------+
| 3 | 2016-11-17 | 6 | 21852 | 17 |
| 6 | 2016-11-17 | 8 | 49434 | 61 |


Also further more, can I have another question within this same query that would further sort this eventual resulting table by the same criteria of sort (score desc, speed asc). Thanks

Answer

A simple method uses a correlated subquery:

select p.* 
from performance p
where p.date = '2016-11-17' and
      p.id = (select p2.id
              from performance p2
              where p2.user_id = p.user_id and p2.date = p.date
              order by score desc, speed asc
              limit 1
             );

This should be able to take advantage of an index on performance(date, user_id, score, speed).

Comments