CODEWITHSUNDEEP
mysqlsqljoinmaxgreatest-n-per-group
user3782821
user3782821

Reputation: 29

SQL meet conditions based on max value

I have to get the player_id and the game_code of the game the user played the most. For each player list the game_code he played the most (not all games he played)

Rules: player_id is unique, and the player could have played multiple games (I want to find out which game he played the most).

I tried with max but I just got lost in trying out.

Here is the fiddle: http://sqlfiddle.com/#!9/0cf0f/2

Upvotes: 0

Views: 56

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

I believe you want this

SELECT t.player_id, t.game_code, t.minutes_played
FROM (
      SELECT player_id, MAX(minutes_played) as maxi
      FROM play_table
      WHERE game_code in ('123','124','125','126','129')
      GROUP BY player_id
) as m
INNER JOIN play_table as t
  ON t.player_id = m.player_id and 
     t.minutes_played = m.maxi

sqlfiddle demo

Upvotes: 1

Thaw
Thaw

Reputation: 861

The other answer is on the right track, but you need to group by player_id in the subquery, assuming you want all of them:

SELECT m.player_id, pt.game_code, pt.minutes_played
FROM (
    SELECT player_id, max(minutes_played) as maxi
    FROM play_table
    GROUP BY player_id
) as m
INNER JOIN play_table pt
ON  m.player_id = pt.player_id
AND pt.minutes_played = m.maxi;

Upvotes: 0

Related Questions