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

Answers


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)

Need Your Help

parsing string for a float

c# parsing decimal

I am reading in an XML file and reading a specific value of 10534360.9

PHP / Javascript create countdown between 2 times

javascript php

I currently have many issues trying to think of how to create this script.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.