mysql SELECT best of each category in a single table

Question

Lets say I have a table called Gamers. Gamers contains the columns GamerID, GameID, Score.

I am interested in selecting the highest scoring player of each game.

For example,

|Gamers
|-------------------------
|GamerID | GameID | Score
|1       | 1      | 10
|2       | 1      | 10
|3       | 1      | 10
|4       | 1      | 90
|5       | 2      | 40
|6       | 2      | 10
|7       | 3      | 10
|8       | 3      | 30

After the query, I hope to get the rows for GamerID 4, 5 and 8. What is the query that would do this?

Answer 1

Try this:

SELECT gamers.*
FROM gamers
INNER JOIN 
 (SELECT 
   max(score) as maxscore, 
   gameid from gamers
   GROUP BY gameid) AS b
ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) ;
ORDER BY score DESC, gameid;

This will output:

+---------+--------+-------+
| gamerid | gameid | score |
+---------+--------+-------+
|       4 |      1 |    90 |
|       5 |      2 |    40 |
|       8 |      3 |    30 |
+---------+--------+-------+
3 rows in set (0.00 sec)

The other option you can do is to create a temporary table or a view (if you don't like sub-query).

create temporary table games_score (
 SELECT max(score) as maxscore, gameid FROM gamers GROUP BY gameid
);

Then:

SELECT gamers.* 
FROM gamers 
INNER JOIN games_score AS b ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) 
ORDER BY score DESC, gameid;

OR a view:

create or replace view games_score AS 
SELECT max(score) as maxscore, gameid FROM gamers GROUP BY gameid;

Then:

SELECT gamers.* 
FROM gamers 
INNER JOIN games_score AS b ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) 
ORDER BY score DESC, gameid;

Answer 2

Try this:

select g1.* from gamers g1
left join gamers g2
on g1.gameId = g2.gameId and g1.score < g2.score
where g2.score is null

Result given provided data:

+---------+--------+-------+
| GAMERID | GAMEID | SCORE |
+---------+--------+-------+
|       4 |      1 |    90 |
|       5 |      2 |    40 |
|       8 |      3 |    30 |
+---------+--------+-------+