Page 1 of 1
Help with stupid query results....
Posted: Sat Feb 21, 2009 4:27 pm
by Citizen
I have an arcade table with columns:
`username`,`gamename`,`score`
To get the highscores for each of the games, I run this query:
Code: Select all
SELECT `username`,`gamename`,MAX(score) FROM `arcade_highscores` GROUP BY `gamename`
It returns the proper highest score for each game, but the username seems to be completely random. Any ideas?
Re: Help with stupid query results....
Posted: Sat Feb 21, 2009 5:05 pm
by Eran
This is how GROUP BY aggregation works. The MAX() functions returns the maximum value for the entire group, but the rest of values are pulled from a random row (similar to what would happen if you did a simple select without an ORDER BY clause). To get the details (username) from the row with the maximum score, you need a add subquery that selects the max score and join against it. Something like:
Code: Select all
SELECT `ah`.`username`,`ah`.`gamename`,`ah`.`score` FROM (
SELECT MAX(`score`) AS maxscore
FROM `arcade_highscores`
GROUP BY `gamename`
) AS scores
INNER JOIN `arcade_highscores` AS `ah` ON `ah`.`score` = scores.maxscore
This is untested, you might need to tweak it to your needs
Re: Help with stupid query results....
Posted: Sat Feb 21, 2009 5:32 pm
by Citizen
Thanks! That did the trick.