MySQL 5.7 query without ROW_NUMBER to get DISTINCT values for one column not working

Question

I'm using the below code snippet to pull distinct values of 'username' from the table 'quiz_scores' which has username, score, and time for a set of quizzes. I'm trying to pull just the best score for each user from the quiz titled 'sportsquiz' using MySQL 5.7.3 - which doesn't have the ROW_NUMBER function which I'd would otherwise use with PARTITION.

The code seems to work just fine as I have it below however it is sorting the results based on username A-Z. I want it to sort based on score DESC then time ASC without sorting based on username - i.e. I want the top score for each user sorted by score, not by user.

Sample Data

username	Score	Time
John	50	1:05
Bob	47	1:25
Judy	49	1:42
Bob	49	1:13
John	50	1:25
Judy	50	1:59

Results wanted:

username	Score	Time
John	50	1:05
Judy	50	1:59
Bob	49	1:13

Results Displayed With Current Code:

username	Score	Time
John	50	1:05
John	50	1:25
Judy	50	1:59
Bob	49	1:13
Judy	49	1:42
Bob	47	1:25

When I remove the 'username ASC' from the ORDER BY I get all scores for all users, i.e. the rn rownumber variable isn't working anymore - I believe maybe I need to have username in ORDER BY for it to work? Even if I put username ASC at the end of the ORDER BY list it still pulls all scores for all users instead of just the top score. Scores are sorted based on 'score' first then on 'time' - so a score of 50 with a time of 2:30 is better than a score of 49 with a time of 1:30.

SELECT username,
       score,
       time  
FROM (
       SELECT *, IF(@prev <> username, @rn:=0,@rn), @prev:=username, @rn:=@rn+1 AS rn
       FROM quiz_scores, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
       WHERE quiz_id = 'sportsquiz'
       ORDER BY username ASC, score DESC, time ASC
     ) t 
WHERE rn = 1;

Answer 1

I think you can use

ROW_NUMBER() OVER(PARTITION BY username ORDER BY username ASC, score DESC, time ASC) AS row_num