By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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),
36 rows affected
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 |