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

if have example scores table:

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

and run query:

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

i receive maximum score number 20, rest of columns returned not same row.

you using max, aggregate function. aggregate functions have effect of treating multiple rows in table group. if don't special, rows in entire table used 1 big group, , when aggregrate function max there, these rows condensed 1 aggregate row. condensing effect have occurred other aggregate functions min, sum, group_concat , friends (see: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html). can apply specific groupings using group by construct, if don't occurrence of aggregate function bunch rows 1 row (but bunching-up occurs after applying where condition, filtered rows aggregated)

now, because of condensing or 'reducing' effect of aggregate functions, there way make 1 value out of many values. max, way list maximum value found instances of expression passed argument max. other columns not have such aggregate function. database products, occurrence of both unaggregated aggregated columns in select list error. mysql behaves wrongly/differently , returns 1 of available values each non-aggregated expression listed in select bit. value mysql - can't rely on particular algorithm.

in many cases, people want "whatever row has maximum value", in other words, find row has value maximum value, use other columns row unaggregated. solution provided middaparka that, , there otherways achieve (google mysql group-wise maximum). more general information aggregate functions , related group by clause, take @ -shameless selfplug- article here: http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html