Search code examples
mysqlgroup-bylimit

Mysql LIMIT in ORDER in GROUP BY

I have this game log table:

+---------+------+-------+
| game_id | user | score |
+---------+------+-------+
| 1       | Nick | 10    |
| 1       | Bob  | 15    |
| 2       | Nick | 15    |
| 2       | Bob  | 10    |
| 3       | Nick | 20    |
+---------+------+-------+

In each game the winner is only one user, who get the greatest score. And I'm trying select each game with its winner, for example:

+---------+--------+
| game_id | winner |
+---------+--------+
| 1       | Bob    |
| 2       | Nick   |
| 3       | Nick   |
+---------+--------+

But all of my trying is without result. Could anyone help me?

Solution

  • You can use the following query:

    SELECT g1.game_id, g1.user AS winner
FROM games AS g1
INNER JOIN (
   SELECT game_id, MAX(score) AS maxScore
   FROM games
   GROUP BY game_id 
) AS g2 ON g1.game_id = g2.game_id AND g1.score = g2.maxScore

    Demo here