Mysql: How to select top 10 users of the last 24 hours for a ranking list (and avoid multiple user entries)

I want to list the top 10 users in the last 24 hours with the highest WPM (words per minute) value.开发者_如何学编程 If a user has multiple highscores, only the highest score should be shown. I got this far:

SELECT results.*, users.username 
FROM results 
JOIN users 
ON (results.user_id = users.id) 
WHERE results.user_id != 0 AND results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
GROUP BY results.user_id 
ORDER BY wpm DESC LIMIT 10

My Problem is, that my code doesn't fetch the highest value. For example:

user x has 2 highscores but instead of selecting the row with the highest wpm for this user, the row with a lower value is selected instead. If I use "MAX(results.wpm)" I get the highest wpm. This would be fine, but I also need the keystrokes for this row. My problem is that even though I fetch the correct user I don't receive the right row for this user (the row which made the user reach the top 10).

This is the results table:

id | user_id | wpm | keystrokes | count_correct_words | count_wrong_words | created

---

(editing answer as we cannot use LIMIT inside a subquery)

Here's another attempt...

SELECT users.username, R1.* 
FROM users 
JOIN results R1 ON users.userId = R1.userId 
JOIN (SELECT userId, MAX(wpm) AS wpm FROM results GROUP BY userId) R2 ON R2.wpm = R1.wpm AND R2.userId = R1.userId
WHERE R1.user_id != 0 AND R1.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
ORDER BY R1.wpm DESC LIMIT 10;

We use max() to first isolate the maximum wpm's for every user_id, then inner join the Results table with this subset to get the full row information.

---

Thanks Oceanic, I think your subquery approach was what gave me the working idea:

The problem was, that GROUP BY picked the first column for the aggregation(?), I now use a subquery to first order the results by wpm and use this "tmp table" for my operation

SELECT t1.*, users.username
FROM (SELECT results.* FROM results WHERE results.user_id != 0 AND results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR) ORDER BY wpm DESC) t1
JOIN users ON (t1.user_id = users.id)
GROUP BY t1.user_id
ORDER BY wpm DESC
LIMIT 10

This seems to work fine.