Select the correct database row to achieve grouped results

Question

I want to select the best score for each user for a specific game.

My current query selects the score, date_saved, and username of the result for skill_game with id 1.

SELECT MAX(score) as score, date_saved, users.username FROM results
INNER JOIN users_results ON results.id = users_results.result_id
INNER JOIN users ON users_results.user_id = users.id
WHERE skill_game_id = 1
GROUP BY user_id
ORDER BY score  DESC

The results are as follows:

| score | username | date_saved |
---------------------------------
|    73 |     Mark | 2021-09-06 |
|    51 |     John | 2018-08-16 |
|    46 |     Ryan | 2020-02-20 |
|    43 |    Chris | 2019-08-27 |
|    40 |   Steven | 2020-07-04 |

Currently, date_saved is not always correct, as Mark's 73-point result was actually saved on 2021-11-03. The following are Mark’s results:

| score | username | date_saved |
---------------------------------
|    73 |     Mark | 2021-11-03 |
|    35 |     Mark | 2021-10-29 |
|    24 |     Mark | 2021-09-06 |

The GROUP BY statement selects the first row in the group, and MAX(score) selects the highest score in the group. I want to be able to select the highest score and select the corresponding date, but I'm not sure how to achieve this in MySQL.

Answer 1

You can also do it by selecting the largest date_saved

SELECT MAX(score) as score, MAX(date_saved), users.username FROM results
INNER JOIN users_results ON results.id = users_results.result_id
INNER JOIN users ON users_results.user_id = users.id
WHERE skill_game_id = 1
GROUP BY user_id
ORDER BY score  DESC