CODEWITHSUNDEEP
mysqlsqlsqlite
vish community
vish community

Reputation: 158

How to make union all SQL-SQLite query by case statement?

I am confused while joining two tables with mulitple columns. I want to find the players IDs from each group who got the maximum score. For example, player 45, 30, and 65 are in group 1 and 45 is the winner with maximum score.

Please help...

Here is table "players"

player_id | group_id
  -----------+----------
   20        | 2
   30        | 1
   40        | 3
   45        | 1
   50        | 2
   65        | 1

Table "matches"

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

I tried:

select distinct gid, (case where...) as winner_id
from
(select a.first_player, a.second_player, a.first_score as fs, 
a.second_score as ss , p.group_id as gid
from
mathches as a
join players as p 
on p.player_id IN (a.first_player,a.second_player))
group by gid

Upvotes: 2

Views: 169

Answers (1)

forpas
forpas

Reputation: 164139

First you must get the total score of each player and then join to players.
Then use FIRST_VALUE() window function to get the top player of each group:

SELECT DISTINCT p.group_id, 
       FIRST_VALUE(p.player_id) OVER (PARTITION BY p.group_id ORDER BY m.score DESC) winner_id
FROM players p
LEFT JOIN (
  SELECT player, SUM(score) score
  FROM (
    SELECT match_id, first_player player, first_score score FROM matches
    UNION ALL
    SELECT match_id, second_player, second_score FROM matches
  ) t
  GROUP BY player
) m ON m.player = p.player_id

See the demo.

Upvotes: 2

Related Questions