php - MySQL Query Unexpected Results -

i trying run mysql query return information highest score group of games. table setup following columns in it: scoreid, score, gameid, playerid, date. when run following sql command gives me proper high score returns first submitted playerid , date. doing wrong here?

select date, max(score) score, scoreid, playerid scores group gameid 

you grouping gameid, returning non-aggregated columns (date, scoreid, playerid). mysql allows select non-aggregated columns in group query, value of columns undetermined (it return first encountered value, not documented , can't rely on it). score aggregated (you using max() aggregation function) value correct.

if want return rows have maximum score should use query this:

select date, score, scoreid, playerid   scores  score = (select max(score) scores) 

or if want return rows have maximum score every gameid, this:

select date, score, scoreid, playerid   scores  (gameid, score) in (select gameid, max(score) scores group gameid) 

please see fiddle here.