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.

Solarwinds
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:

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

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:

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

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.

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

Solarwinds
, , ,
Previous Post
How to List All the Nonclustered Indexes of Your Table? – Interview Question of the Week #155
Next Post
How to Find DISTINCT COUNT of Column Rows Without Using Distinct Count Keywords? – Interview Question of the Week #157

Related Posts

3 Comments. Leave new

  • In. MySQL do we have ranking functionality. Pls give example.. I am asking all functionality in ranking

    Reply
  • What about this?

    SELECT * FROM #StudentClass AS A
    WHERE A.Marks IN (SELECT TOP 2 Marks FROM #StudentClass AS X WHERE X.ClassName = A.ClassName ORDER BY x.Marks DESC)

    Reply
  • Hi Pinal,

    I would prefer to use DENSE_RANK() over ROW_NUMBER(), as we might have more than one student in the class with same marks.

    Thanks,
    Srini

    Reply

Leave a Reply

Menu