mysql: select max(score) doesn't return the relevant row data. how to solve the isssue ?

Question

Hi.

if i have for example a scores table:

    user game score timestamp
    1    50   50    date
    2    60   40    date
    3    70   25    date
    4    80   18    date

and i run the query:

select user, game, max(score), timestamp from scores

i will receive the maximum score number that is 20, but the rest of the columns that are returned are not from that same row.

why is that and how can i resolve the issue ?

Answer 1

As you're not using a GROUP clause, the max function is returning the maximum value from across the table. As such, if you want to return the matching data for the highest score, you'd need to:

SELECT * FROM table_name ORDER BY score DESC LIMIT 1

Answer 2

Your query works as designed: It lists users, and the highest overall score.

To get the user with the highest score, you could use

select user, game, score, timestamp from scores ORDER by score DESC limit 1

Answer 3

You are using MAX, which is an aggregate function. Aggregate functions have the effect of treating multiple rows in the table as a group. If you don't do anything special, all rows in the entire table will be used as one big group, and when an aggregrate function like MAX is there, all these rows will be condensed into just one aggregate row. That condensing effect would have occurred also for other aggregate functions like MIN, SUM, GROUP_CONCAT and friends (see: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html). You can also apply specific groupings using the GROUP BY construct, but if you don't the occurrence of the aggregate function will just bunch up all rows into one row (but this bunching-up occurs after applying the WHERE condition, so only the filtered rows are aggregated)

Now, because of this condensing or 'reducing' effect of aggregate functions, there as to be some way to make one value out of many values. For MAX, that way is to list only the maximum value found for all instances of the expression you passed as argument to MAX. But your other columns do not have such an aggregate function. For most database products, the occurrence of both unaggregated as well as aggregated columns in the SELECT list would be an error. But MySQL behaves wrongly/differently and returns just one of the available values for each non-aggregated expression listed in the SELECT bit. Which value is up to mysql - you can't rely on any particular algorithm.

In many cases, people want to do something with "whatever row that has the maximum value", in other words, find the row that has as value the maximum value, but use the other columns from that row unaggregated. The solution provided by middaparka does that, and there are otherways to achieve that too (google for MySQL group-wise maximum). For more general information about aggregate functions and the related GROUP BY clause, you could take a look at -shameless selfplug- my article here: http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html