GROUP BY as a way to pick the first row from a group of similar rows, is this correct, is there any
Posted
by FipS
on Stack Overflow
See other posts from Stack Overflow
or by FipS
Published on 2010-04-05T15:49:05Z
Indexed on
2010/04/05
16:33 UTC
Read the original article
Hit count: 360
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)
© Stack Overflow or respective owner
