GROUP BY as a way to pick the first row from a group of similar rows, is this correct, is there any
- by FipS
I have a table which stores test results like this:
user | score | time
-----+-------+------
aaa | 90% | 10:30
bbb | 50% | 9:15 ***
aaa | 85% | 10:15
aaa | 90% | 11:00 ***
...
What I need is to get the top 10 users:
user | score | time
-----+-------+------
aaa | 90% | 11:00
bbb | 50% | 9:15
...
I've come up with the following SELECT:
SELECT * FROM (SELECT user, score, time
FROM tests_score
ORDER BY user, score DESC, time DESC) t1
GROUP BY user
ORDER BY score DESC, time
LIMIT 10
It works fine but I'm not quite sure if my use of ORDER BY is the right way to pick the first row of each group of sorted records. Is there any better practice to achieve the same result? (I use MySQL 5)
