Get top records for each group in sql

Question

I have a table which has 3 columns StudentID, Score and ClassNumber. What I need now is to select top 5 students of each Class (according to their Score result).

For example if there are 20 students in Class1, 40 students in Class2 and students in Class3, I need to select 15 top score students for each Class(5 for Class1, 5 for Class2, 5 for Class3)

How can I do this in one SQL query?

Answer 1

Do you mean somthing like this?

with tmp as
(
    select ClassNumber,
           StudentID,
           Score,
           row_number() over (partition by ClassNumber order by Score desc) row_num, 
    from Student s
)

select ClassNumber, StudentID, Score
from tmp
where row_num <= 5
order by ClassNumber