mysql: select max(score) doesn't return the relevant row data

Question

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.

Answer 1

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