Solving max Scoring player from each group in mySQL

Question

Refer to another Stack Overflow question here, however the answers there didn't include the group_id 3 player. I tried to replicate the answer in MySQL but I am not familiar with PostgreSQL. Anyone can show how to proceed it in MySQL?

The question is to return the max scored player as winner_id from each group

create table players (
      player_id integer not null unique,
      group_id integer not null
  );

  create table matches (
      match_id integer not null unique,
      first_player integer not null,
      second_player integer not null,
      first_score integer not null,
      second_score integer not null
  );

insert into players values(20, 2);
insert into players values(30, 1);
insert into players values(40, 3);
insert into players values(45, 1);
insert into players values(50, 2);
insert into players values(65, 1);
insert into matches values(1, 30, 45, 10, 12);
insert into matches values(2, 20, 50, 5, 5);
insert into matches values(13, 65, 45, 10, 10);
insert into matches values(5, 30, 65, 3, 15);
insert into matches values(42, 45, 65, 8, 4);

matches table

match_id | first_player | second_player | first_score | second_score
  ----------+--------------+---------------+-------------+--------------
   1        | 30           | 45            | 10          | 12
   2        | 20           | 50            | 5           | 5
   13       | 65           | 45            | 10          | 10
   5        | 30           | 65            | 3           | 15
   42       | 45           | 65            | 8           | 4

Expected output

group_id | winner_id
  ----------+-----------
   1        | 45
   2        | 20
   3        | 40

`Partition by` is not supported until MySQL version 8. So either upgrade, or use a work-around like this; https://stackoverflow.com/a/20432371/8194026 — John Mitchell, Aug 19 '20 at 13:35

score 0 · Answer 1 · answered Aug 20 '20 at 00:12

I presume that since you can't use the solution to the other question that you are using MySQL 5.7 or below. In that case, you have to simulate the ROW_NUMBER/PARTITION functionality, which you can do with a LEFT JOIN from a derived table of scores per player with itself, joining on the score being greater than that in the first table. Any player who has no scores greater in the joined table clearly has the highest score. Since there can be ties, we then take the minimum of the player_id values from that table (when there is no tie, this has no effect).

SELECT group_id, MIN(player_id) AS player_id
FROM (
  SELECT t1.group_id, t1.player_id
  FROM (
    SELECT p.player_id, p.group_id,
           SUM(CASE WHEN m.first_player = p.player_id THEN m.first_score
               ELSE m.second_score
               END) AS score
    FROM players p
    LEFT JOIN matches m ON m.first_player = p.player_id OR m.second_player = p.player_id
    GROUP BY p.player_id, p.group_id
  ) t1
  LEFT JOIN (
    SELECT p.player_id, p.group_id,
           SUM(CASE WHEN m.first_player = p.player_id THEN m.first_score
               ELSE m.second_score
               END) AS score
    FROM players p
    LEFT JOIN matches m ON m.first_player = p.player_id OR m.second_player = p.player_id
    GROUP BY p.player_id, p.group_id
  ) t2 ON t2.group_id = t1.group_id AND t2.score > t1.score
  GROUP BY t1.group_id, t1.player_id
  HAVING COUNT(t2.player_id) = 0
) w
GROUP BY group_id

Output:

group_id    player_id
1           45
2           20
3           40

Demo on db-fiddle

@NordicFox Did this answer your question? If not, could you provide more information to help answer it? — Nick, Aug 23 '20 at 06:59

score 0 · Answer 2 · answered Aug 09 '23 at 20:31

select group_id, player_id from (
select *, row_number() over(partition by A.group_id order by score DESC, player_id ASC) as ranking
from(
select player_id, group_id, sum(case when player_id=first_player then first_score 
                                else second_score end) as score
from players p
left join
matches m
on p.player_id=m.first_player or p.player_id=m.second_player
group by p.player_id, p.group_id)A)B
where B.ranking=1;

output:

group_id | winner_id
  ----------+-----------
   1        | 45
   2        | 20
   3        | 40

Solving max Scoring player from each group in mySQL

2 Answers2