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 |
CodePudding user response：
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.