multiple selects with different where conditions and limits-CodePudding

I have a high scores table that is slightly more complicated because scores are tracked in rounds (round 1, round 2, round 3, etc.). Sample table:

scoreID	roundID	userID	score
1	1	2	25
2	1	3	12
3	1	4	14
4	1	5	6
5	2	2	39
6	2	3	23
7	2	4	13
8	2	5	26

There can be many more rounds, and many more users.

I would like to pull the top 3 user scores from each round. My select statement at the moment looks like this:

select `scores`.`score`, `users`.`username`, `scores`.`roundID` 
FROM `scores` 
INNER JOIN `users` on `users`.`user_id` = `scores`.`userID` 
ORDER BY `scores`.`score` DESC LIMIT 3;

However, this returns a result like so:

score	username	roundID
39	joey	2
26	bubba	2
25	george	1

when what I want is the top 3 scores per round:

score	username	roundID
25	george	1
14	bubba	1
12	joey	1
39	george	2
26	homey	2
23	joey	2

How do I select the top 3 scores in each round so my result mirrors the table immediately above?

CodePudding user response：

You would do this like:

select score, username, roundID
from (
    select
        score, userID, roundID,
        rank() over (partition by roundID order by score desc) score_rank
    from score
) ranked_scores
inner join users on users.user_id = ranked_scores.userID 
where score_rank <= 3
order by roundID, score, username