Show 1st and 2nd place for each game in one query-CodePudding

I am working on an SQL exercise and need some help. I'm a beginner, familiar with MySQL and T-SQL and would really appreciate an explanation on how to solve it.

What I have:

Two tables:

What I need:

Find out 1st place and 2nd place for each game, preferably using a single query. Querying this for one game? Sure, I know how to do that. Can't figure out how to achieve it in a single query for all games, though.

Query result should look like that:

Code to create the tables:

CREATE TABLE players (
    player_id int,
    name varchar(255)
    );

CREATE TABLE games (
    game_id int,
    player_id int,
    game varchar(255),
    score int
    );

INSERT INTO players VALUES
(110, 'Liam'),
(111, 'Alex'),
(112, 'Scott'),
(113, 'Erin'),
(114, 'Bradley'),
(115, 'Samantha'),
(220, 'Spencer'),
(221, 'Everly'),
(222, 'Reagan'),
(223, 'Rosalie'),
(224, 'Brenton'),
(225, 'Erika'),
(330, 'Cody'),
(331, 'Lara'),
(332, 'Chandler'),
(333, 'Nayeli'),
(334, 'Joseph'),
(335, 'Reyna');

INSERT INTO games VALUES
(550, 110, 'Galaxy', 56),
(550, 111, 'Galaxy', 100),
(660, 112, 'Racing', 99),
(660, 113, 'Racing', 73),
(440, 114, 'Simulator', 91),
(440, 115, 'Simulator', 65),
(550, 220, 'Galaxy', 95),
(550, 221, 'Galaxy', 92),
(660, 222, 'Racing', 96),
(660, 223, 'Racing', 57),
(440, 224, 'Simulator', 89),
(440, 225, 'Simulator', 70),
(550, 330, 'Galaxy', 90),
(550, 331, 'Galaxy', 83),
(660, 332, 'Racing', 91),
(660, 333, 'Racing', 88),
(440, 334, 'Simulator', 94),
(440, 335, 'Simulator', 68);

CodePudding user response：

A combination of CTE JOIN and ROW_NUMBER will gve you the correct answer Of course there are also RANK and DENSE_RANK, that will help with ties if ROW_NUMBER wound give the right answer

WITH CTE as (
SELECT
  g.game_id,g.game,p.name, g.score,
  ROW_NUMBER() OVER(PARTITION BY game_id ORDER by score DESC) rn
FROM games g JOIN players p ON g.player_id = p.player_id)
SELECT game,name,score FROM CTE
WHERE rn <= 2
ORDER BY game_id, score DESC

game	name	score
Simulator	Joseph	94
Simulator	Bradley	91
Galaxy	Alex	100
Galaxy	Spencer	95
Racing	Scott	99
Racing	Reagan	96

fiddle

CodePudding user response：

The window functions can be invaluable... well worth your time getting comfortable with time

Example

Select G.Game
      ,P.Name
      ,G.Score
 From (
        Select *
              ,RN = row_number() over (partition by game_id  order by score desc)
         From  Games
      ) G
 Join Players P on P.player_id = G.player_id 
 Where G.RN<=2

If you want to see TIES, you can use dense_rank() instead