How to Get Top N Records Per Group? – Ranking Function – Interview Question of the Week #156

Question: How to Get Top N Records Per Group? (using Ranking Function)

Answer: During a recent interview, I had given this simple puzzle to over 40 candidates. This simple puzzle depends on Ranking Window Functions.

First, let us create a sample record and right after that, we will understand the problem.

In sample records, we are creating a simple table with four columns containing Student Name, Class Name along with the marks each student got.

CREATE TABLE StudentClass
(ID INT,
StudentName VARCHAR(100),
ClassName VARCHAR(100),
Marks INT)
GO
INSERT INTO StudentClass
SELECT 1, 'Roger', 'Science', 50
UNION ALL
SELECT 2, 'Sara', 'Science', 40
UNION ALL
SELECT 3, 'Jimmy', 'Science', 30
UNION ALL
SELECT 4, 'Mike', 'Maths', 50
UNION ALL
SELECT 5, 'Mona', 'Maths', 30
UNION ALL
SELECT 6, 'Ronnie', 'Maths', 10
UNION ALL
SELECT 7, 'James', 'Art', 50
UNION ALL
SELECT 8, 'Mona', 'Art', 35
UNION ALL
SELECT 9, 'Roger', 'Art', 25
GO
SELECT *
FROM StudentClass
GO

When you run above script it will give us following resultset:

If you want to get top 2 records (top n records) for each group, you can write complicated code for it or just used ranking windows function. In the following script, I have used windows function and have listed top 2 students from each class based on their marks.

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY ClassName
ORDER BY Marks DESC) AS StRank, *
FROM StudentClass) n
WHERE StRank IN (1,2)
GO

You can see in the above example I have used ROW_NUMBER() ranking function. As I wanted to get the top student with the highest score, I have used ORDER BY Marks DESC and I want top two students from each class hence used keyword PARTITION BY ClassName.

When you execute above script it gives us necessary results as displayed in the following image:

I guess that’s it. Though this script may be difficult to understand in the beginning it is indeed quite simple once you get used it.

Here are few additional resources on the same subject.

• SQL SERVER – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35
• SQL SERVER – Introduction to PERCENT_RANK() – Analytic Functions Introduced in SQL Server
• SQL SERVER – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE 
• SQL SERVER – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER
• SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

Reference: Pinal Dave (https://blog.sqlauthority.com)