Help with stupid query results....

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Help with stupid query results....

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Help with stupid query results....

Post 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
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Re: Help with stupid query results....

Post by Citizen »

Thanks! That did the trick.
Post Reply