Find the top 2 records for each key in a table

Question

I have a list of results of player's scores in games, and I need to get the first two finishers for each game. LIMIT 2 works for the result set as a whole, but I need to limit it to 2 (or 1 if there is only one) per game.

Table being queried:

game_id	player_id	score
1	10	100
1	20	300
1	30	200
2	40	100
2	50	200

Desired results:

game_id	player_id	score
1	20	300
1	30	200
2	50	200
2	40	100

Answer 1

Using RANK() we can try:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY game_id ORDER BY score DESC) rnk
    FROM yourTable
)

SELECT game_id, player_id, score
FROM cte
WHERE rnk <= 2
ORDER BY game_id, score DESC;

Note that if there be the possibility of ties, then you might want to use DENSE_RANK instead of RANK. If ties are not a concern, then you could also use ROW_NUMBER instead of RANK.