CODEWITHSUNDEEP
mysqlsql
Morpork
Morpork

Reputation: 551

mysql SELECT best of each category in a single table

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?

Upvotes: 2

Views: 743

Answers (5)

Sergi
Sergi

Reputation: 1

Look here http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Upvotes: 0

Book Of Zeus
Book Of Zeus

Reputation: 49895

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;

Upvotes: 6

John Woo
John Woo

Reputation: 263933

try this: 

SELECT a.GameID, a.GamerID, a.Score
FROM Gamers a INNER JOIN
    (
        SELECT s.GameID, MAX(s.Score) AS Maxx
        FROM Gamers s
        GROUP BY s.GameID
    ) b ON (a.GameID = b.GameID) AND (a.Score = b.Maxx) 
ORDER BY GameID;

Upvotes: 4

Michael Robinson
Michael Robinson

Reputation: 29508

You could accomplish this using a sub-select, like below:

SELECT GameID, GamerID, Score
FROM Gamers
WHERE Score = (
     SELECT
     MAX(scores.Score)
     FROM Gamers AS scores
     WHERE scores.GameID = Gamers.GameID
)
GROUP BY GameID

Tested on:

CREATE TABLE Gamers (
  GamerID int(11) NOT NULL,
  GameID int(11) NOT NULL,
  Score int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO Gamers (GamerID, GameID, Score) VALUES
(1, 1, 10),
(2, 1, 10),
(3, 1, 10),
(4, 1, 90),
(5, 2, 40),
(6, 2, 10),
(7, 3, 10),
(8, 3, 10);

Of course this won't be ideal in situations where two gamers achieve the same score for a given game.

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

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 |
+---------+--------+-------+

Upvotes: 5

Related Questions