CODEWITHSUNDEEP
sqlpostgresqlselectmaxgreatest-n-per-group
D-hash-pirit
D-hash-pirit

Reputation: 437

How to get max scoring player from each group using SQL?

I have tables players and matches and I want to find players with max points, note that player with lower id is winner in each group if scores matches. 

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);

Now I want result as

Note that first and second player can be same from group.

Result:-

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

I am unsure how to proceed.

Upvotes: 0

Views: 4485

Answers (5)

Ahmed Alnaqa
Ahmed Alnaqa

Reputation: 23

This is the right answer all the above just give 90% from the needed you can check it here

https://www.db-fiddle.com/f/2Lp3LoMpXVefUbezRKBY3N/0

select group_id,player_id from
(
select
group_id
,P.player_id
,sum(points) as total
,DENSE_RANK () OVER ( 
            PARTITION BY group_id
            ORDER BY sum(points) desc, P.player_id asc
        )P_Rank 
from
(
select c.match_id, t.*
from matches c
  cross join lateral (
     values 
       (c.first_player, c.first_score),
       (c.second_player, c.second_score)
  ) as t(player, points)
order by match_id, points
) base
right join players P
on base.player = P.player_id
group by 1,2
order by 1
)BT
where P_Rank=1

Upvotes: 0

Lovi Singh
Lovi Singh

Reputation: 1

SELECT GROUP_ID, PLAYER_ID, MAX_SCORE FROM (
SELECT GROUP_ID, PLAYER_ID, MAX_SCORE, DENSE_RANK() OVER(PARTITION BY GROUP_ID ORDER BY MAX_SCORE DESC,PLAYER_ID ASC) RANK
FROM
(
SELECT DISTINCT GROUP_ID, PLAYER_ID, COALESCE(MAX(TOT_SCORE)OVER (PARTITION BY PLAYER_ID),0)MAX_SCORE 
FROM 
(
SELECT PLAYER_ID, GROUP_ID, TOT_SCORE FROM 
   (
SELECT PLAYER, SUM(SCORE) AS TOT_SCORE
FROM 
     (
SELECT FIRST_PLAYER AS PLAYER,SUM(FIRST_SCORE)AS SCORE  FROM MATCHES GROUP BY FIRST_PLAYER
UNION 
SELECT SECOND_PLAYER AS PLAYER,SUM(SECOND_SCORE)AS SCORE  FROM MATCHES GROUP BY SECOND_PLAYER
      )
GROUP BY PLAYER 
   )S
RIGHT JOIN 
PLAYERS P
ON
S.PLAYER=P.PLAYER_ID
))) WHERE RANK=1

Upvotes: 0

Sarath Kumar
Sarath Kumar

Reputation: 21

Approach taken:

For table 2 we will be getting the winner and corresponding score for each player and then we will group the players to calculate the overall sum

After this above table will be joined with table 1 which will give the score for each player in the groups, we will sort this data based on group and score

Then finally we will select the first record of each group which will give the required results

with actual_data as 
(select row_number()over(partition by group_id 
order by group_id asc, 
coalesce(table2.sum_score, 0) desc) as rn, 
group_id, player_id, coalesce(table2.sum_score, 0) as score 
from  players as table1
left join 
(select player, sum(score) as sum_score from ( 
select match_id, 
case 
when first_score > second_score then first_player
when first_score = second_score and first_player < second_player 
then first_player 
else second_player end as player, 
case 
when first_score > second_score then first_score 
when first_score = second_score and first_player < second_player 
then first_score 
else second_score end as score 
from matches) as foo 
group by player 
order by sum_score desc) as table2 
on table1.player_id = table2.player 
order by group_id asc,  coalesce(table2.sum_score, 0) desc 
) 
select group_id , player_id from actual_data where rn = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I think the simplest approach is a lateral join and distinct on:

select distinct on (p.group_id) p.group_id, v.player, v.score
from matches m cross join lateral
     (values (first_player, first_score), (second_player, second_score)
     ) v(player, score) join
     players p
     on v.player = p.player_id
order by p.group_id, v.score desc;

Here is a db<>fiddle.

Upvotes: 2

GMB
GMB

Reputation: 222442

Use row_number():

select group_id, player_id
from (
    select
        p.*,
        row_number() over(
            partition by p.group_id 
            order by case 
                when m.first_player = p.player_id then m.first_score 
                else m.second_score 
            end desc,
            player_id
        ) rn
    from players p
    inner join matches m
        on m.first_player = p.player_id or m.second_player = p.player_id
) x
where rn = 1

Demo on DB Fiddle:

| group_id | player_id |
| -------- | --------- |
| 1        | 65        |
| 2        | 20        |

Note: there is just one player in group 3 (player_id 40), and they didn't participate any game.

Upvotes: 2

Related Questions