0

I want to select the best scores per user for a specific game.

My current query selects the score, date_saved and username of the results of 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

And the results are:

| 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 |

Right now the date_saved is not always correct as Mark's 73 score result was actually saved on 2021-11-03. Below the results of Mark:

| 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 of 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 with it but I'm not sure how to do this with MySQL.

Jop
  • 3
  • 3
  • 'The GROUP BY username statement selects the first row ' - that should be regarded as coincidence - the actual date is non determinate. see https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html for more on why this may be, You should change your query to test the max score for user in a correlated sub query. – P.Salmon Jan 19 '22 at 10:36
  • MySql is the only RDBMS afaik that allows this to be valid [mysql_only_full_group_by](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by) – Stu Jan 19 '22 at 10:37
  • Does this answer your question? [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) – P.Salmon Jan 19 '22 at 10:39

1 Answers1

0

You can do it by also selecting the max 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
SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • I don't need the latest date, I need the date of the row with the highest score – Jop Jan 19 '22 at 12:06
  • @Jop did you tried my query ? it will result the dates of the highest score not the latest date. – SelVazi Jan 19 '22 at 12:52
  • I tried it, and by coincidence it shows the correct date for the first row, but not for the other ones... – Jop Jan 19 '22 at 13:25