MySQL Query Unexpected Results
I am trying to run a MySQL query to return some information about the highest score for a group of games. The table is setup with the following columns in it: scoreid, score, gameid, playerid, date. When I run the following SQL command it gives me the proper high score but returns the first submitted playerid and date. What am I doing wrong here?
SELECT date, MAX(score) as score, scoreid, playerid FROM scores GROUP BY gameid
You are grouping by gameid, but you are returning some non-aggregated columns (date, scoreid, playerid). MySQL allows you to select non-aggregated columns in a group by query, but the value of those columns will be undetermined (it will usually return the first encountered value, but this is not documented and you can't rely on it). Score is aggregated (you are using MAX() which is an aggregation function) so its value will be correct.
If you want to return all rows that have the maximum score you should use a query like this:
SELECT date, score, scoreid, playerid FROM scores WHERE score = (SELECT MAX(score) FROM scores)
or if you want to return the rows that have the maximum score for every gameid, something like this:
SELECT date, score, scoreid, playerid FROM scores WHERE (gameid, score) IN (SELECT gameid, MAX(score) FROM scores GROUP BY gameid)
Please see fiddle here.
The MAX(score) won't get the row that has the max score. It will simply return the max score, but all the other fields can simply belong to another row.
You can better try:
SELECT date, score, scoreid, playerid FROM scores GROUP BY gameid HAVING score = MAX(score)