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?

For which database and version ? For Oracle and SqlServer you have the row_number window function that can group and enumerate records within the group. Then you can query the top(x) from each. — Amir Pelled
– Amir Pelled, Commented Mar 21, 2018 at 10:24
@AmirPelled: window functions are not limited to Oracle and SQL Server. Every modern DBMS supports them. Even MySQL will arrive in the 21st century with the upcoming version 8. — user330315
– user330315, Commented Mar 21, 2018 at 10:26
Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Mar 21, 2018 at 10:26

Stack Overflow · Accepted Answer · 2018-03-21 10:38:49Z

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

edited Mar 21, 2018 at 10:38

answered Mar 21, 2018 at 10:28

Stack Overflow

2,8126 gold badges26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gaurav Over a year ago

Did you consider scenario where more than one student have same score?

Stack Overflow Over a year ago

Its select only 5 students for class, Test it. If there is any preference for this case - can add the relevant column to the order section within the row_number() function.

Nishant Gupta · Accepted Answer · 2018-03-21 10:39:18Z

Solution in MYSQL :

SELECT StudentID, Score, ClassNumber FROM (SELECT StudentID, Score, ClassNumber, @class_rank := IF(@current_class = classNumber, @class_rank + 1, 1) AS class_rank, @current_class := ClassNumber FROM student ORDER BY ClassNumber, score DESC ) ranked WHERE class_rank <= 5;

Solution in SQL SERVER:

select ClassNumber, StudentID, Score from ( select ClassNumber, StudentID, Score, dense_rank() over (partition by ClassNumber order by Score desc) ranking from Student s ) as t where ranking <= 5 order by ClassNumber

Collectives™ on Stack Overflow

Get top records for each group in sql

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related