I have this query:

select name, score from tb
group by name, score
order by score desc
limit 5

and the output is:

name1   90
name2   85
name3   70
name4   50 
name5   30

Now I want to do the same query but using the names to perfom another query. I need to know how to "call" the 2ns highest score name, the 3th, 4th and 5th name in thesame order they appear in the first query.

something like this:

with 5_top as(  
select name, score from tb
    group by name, score
    order by score desc
    limit 5)

select <2nd name_here> from 5_top

So from the first query I know name2 is the 2nd highest score so in the next query I can just use where name = name2 but if name2 next week drops to 3th position my query will fail.

CodePudding user response：

You can use row_number() window function to determine the the place. Note the explicit ordering for it:

WITH dataset (name, score) AS (
    VALUES ('name1', 90),
        ('name2', 85),
        ('name3', 70),
        ('name4', 50),
        ('name5', 30)
)
select * 
from (
        select *, row_number() over (order by score desc) rnk
        from dataset
    )
where rnk > 1

Output: