[SOLVED] MAX()

[jaymc]

I have a table which contains game scores

 

Each game is allowed 10 scores. There are 10 games. The scores table has 100 records..

 

I want to create a query that will pull out the top score for each game, heres what I tried

 

SELECT game_id, username, MAX(score) score
FROM arcade_scores

 

The MAX(score) works as it pulls out the highest score but it does not pull the same username from that row. Thats where I am stuck

 

Any ideas?

 

 

 

[Mchl]

SELECT game_id, username, score
FROM arcade_scores GROUP BY game_id HAVING MAX(score)

 

edit

 

No... apprentyl not...

 

[corbin]

SELECT game_id, username, MAX(score) score FROM arcade_scores GROUP BY game_id ORDER BY score DESC LIMIT 10;

 

 

That might work, but I doubt it.

[fenway]

SELECT game_id, username, MAX(score) score FROM arcade_scores GROUP BY game_id ORDER BY score DESC LIMIT 10;

 

 

That might work, but I doubt it.

No, it won't work.

 

This is the MOST common question asked on this forum.  What you have to remember is that using "group by" means that mysql throws away every other column in the table -- and just tallies the results of the aggregate function you requested, like MAX, COUNT, etc.

 

That means that you CANNOT get back any other columns at the same time... you'll need another query.  The human brain does this in two steps as well, you just don't notice: you find the max value by looking at every row ONCE, and THEN you find the matching username by looking at every row AGAIN to find that one with the max value.

 

So:

 

SELECT t1.game_id, t1.score, t1.username
FROM arcade_stores AS t1 
INNER JOIN
( SELECT game_id, MAX(score) AS score
FROM arcade_scores GROUP BY game_id ) AS t2 USING ( game_id, score )

[corbin]

SELECT game_id, username, MAX(score) score FROM arcade_scores GROUP BY game_id ORDER BY score DESC LIMIT 10;

 

 

That might work, but I doubt it.

No, it won't work.

 

This is the MOST common question asked on this forum.  What you have to remember is that using "group by" means that mysql throws away every other column in the table -- and just tallies the results of the aggregate function you requested, like MAX, COUNT, etc.

 

That means that you CANNOT get back any other columns at the same time... you'll need another query.  The human brain does this in two steps as well, you just don't notice: you find the max value by looking at every row ONCE, and THEN you find the matching username by looking at every row AGAIN to find that one with the max value.

 

So:

 

SELECT t1.game_id, t1.score, t1.username
FROM arcade_stores AS t1 
INNER JOIN
( SELECT game_id, MAX(score) AS score
FROM arcade_scores GROUP BY game_id ) AS t2 USING ( game_id, score )

 

 

Yeah, that's what I thought....

 

 

Today's been an off day.... lol.

[jaymc]

Once again fenway strikes

 

Thanks.