SQL help - Get top 5 users by highest score on any particular day

Question

I have a query that gets top 5 accumulated score from the results table.

SELECT qm_1_users.userId, 
       qm_1_users.fname, 
       qm_1_users.lname, 
       SUM(qm_1_results.points) as topScore
  FROM qm_1_results
       INNER JOIN qm_1_users 
           ON qm_1_users.userid=qm_1_results.userid
GROUP BY 
       qm_1_users.userId
ORDER BY 
       topScore DESC 
LIMIT 5;

I now need it to show the top 5 users who have the highest score achieved on any particular day. So if I played the quiz three times and achieved the following scores (Day 1 = 400, Day 2 = 600, Day 3 = 120), then my top score would be 600. This is where my SQL knowledge struggles.

quizid    |   userid    |    questionid     |    points
1             1              1                   50
1             1              2                   50
2             1              1                   50
2             1              2                   50
2             1              3                   50
2             1              4                   50
4             1              1                   50
4             1              2                   50
4             1              3                   50
4             1              3                   50
4             1              4                   50
4             1              5                   50
1             2              1                   50
3             2              1                   50
3             2              2                   50
4             2              1                   50
4             2              2                   50
4             2              3                   50
1             3              1                   50
1             3              1                   50
1             3              1                   50

Player 1: Day 1 – 100 points, Day 2 – 200 points, Day 4 – 300 points Player 2: Day 1 – 50 points, Day 3 – 400 points, Day 4 – 150 points Player 3: Day 1 – 150 points

The top of the leaderboard would show:

Player 2 – 400 points in position 1 Player 1 – 300 points in position 2 Player 3 – 150 points in position 3

Can anyone help or point me in the right direction on how to do this?

Answer 1

You can group by user and day so you'll get the same result classified by day/person, then you can order by user_id and topScore (but not necessary).

SELECT qm_1_users.userId, 
       qm_1_results.day, 
       SUM(qm_1_results.points) as topScore
FROM qm_1_results
       INNER JOIN qm_1_users 
           ON qm_1_users.userid=qm_1_results.userid
GROUP BY 
       qm_1_users.userId, qm_1_results.day
ORDER BY 
       user_id, topScore DESC 

By this way you'll get something like

user1 day1 500
user1 day2 412
user1 day3 660
user2 day1 440
user2 day2 613
user2 day3 360

If you add a filter per day at where section you can select just this day top five with limit and order. Something like WHERE qm_1_results.day = '2014-11-05' I suposse

Answer 2

I've finally worked it out. Thanks for all the pointers.

SELECT DISTINCT(userId), quizid, fname, lname, image, userTypeId, iso, MAX(topScore) as topScore FROM (
        SELECT qm_1_users.userId,
               qm_1_users.fname, qm_1_users.lname, qm_1_users.image, qm_1_users.userTypeId,qm_1_results.quizid, SUM(qm_1_results.points) as topScore, wm_countries.iso
               FROM qm_1_results
               INNER JOIN qm_1_users ON qm_1_users.userid=qm_1_results.userid
       LEFT JOIN wm_countries ON wm_countries.countryId=qm_1_users.countryId
            GROUP BY qm_1_users.userId, qm_1_results.quizid
            ORDER BY topScore DESC, userid DESC
        ) AS x GROUP BY userId
    ORDER BY topScore DESC
LIMIT 5;