Problem with MySQL query for high scores leaderboard

Question

Hello,

I have a MySQL high scores table for a game that shows the daily high score for each of the past days of the year. Right now I am doing a PHP for-loop and making a separate query for each day, but the table is becoming too large to do that so I would like to condense it into one simple MySQL statement.

Here is my new query right now (date_submitted is a timestamp):

SELECT date(date_submitted) as subDate, name, score FROM highScores WHERE date_submitted > "2009-07-16" GROUP BY subDate ORDER BY subDate DESC, score DESC LIMIT 10;

output:

+------------+------------+--------+
| subDate    | name       | score  |
+------------+------------+--------+
| 2010-07-18 | krissy     | 959976 |
| 2010-07-10 | claire     | 260261 |
| 2010-07-05 | krissy     | 771416 |
| 2010-06-19 | krissy     | 698031 |
| 2010-06-18 | otli       | 264898 |
| 2010-06-15 | robbie     |  82303 |
| 2010-06-01 | dad        | 480469 |
| 2010-05-29 | vicente    | 124149 |
| 2010-05-27 | dad        | 564007 |
| 2010-05-26 | caleb      | 502623 |
+------------+------------+--------+

My problem is that when it grouped by subDate, it took the highest score for the earliest timestamp of that day, as you can see in the next query:

SELECT name, score, date_submitted FROM highScores WHERE date(date_submitted)='2010-06-15' GROUP BY name ORDER BY score DESC;

output:

+--------+--------+---------------------+
| name   | score  | date_submitted      |
+--------+--------+---------------------+
| john   | 304095 | 2010-06-15 22:58:02 |
| april  | 247126 | 2010-06-15 21:25:31 |
| orli   | 166021 | 2010-06-15 21:25:31 |
| robbie |  82303 | 2010-06-15 11:38:39 |
+--------+--------+---------------------+

As you can see, poor john should have been the leader for 2010-06-15. Can anyone help? Hopefully it is something real simple I am overlooking. I tried using max(score) before the FROM part in the 1st query and it gave me the correct score but didn't carry over the name.

Thank you for any help.

Answer 1

Add a

ORDER BY userScore DESC 

at the end of the second query.

Answer 2

SELECT userName, userScore, subDate FROM (
    SELECT 
        userName,
        userScrore,
        DATE(submitDate) as subDate,
        @rn := CASE WHEN @subDate = DATE(submitDate)
                THEN @rn + 1
                ELSE 1
           END AS rn,
        @subDate := DATE(submitDate)
    FROM (SELECT @subDate := NULL) vars, highScores
    ORDER BY submitDate, userScore DESC
) deriv
WHERE rn=1;

See also the answer to another 'highest record per something'-question