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

Question: How do you return the top two students in each class, ranked by marks?

Answer: Use ROW_NUMBER() partitioned by class, then keep row numbers 1 and 2. I gave this puzzle to more than 40 interview candidates. It is a small problem with one important word: each. A single TOP (2) gives two students for the whole table, while the question asks for two students in Science, two in Maths, and two in Art.

Here is the original nine-student example, moved into a temporary table so it can be tried without leaving a permanent StudentClass table behind. The names, classes, and marks are the same as in the interview puzzle.

CREATE TABLE #StudentClass
(
    ID int NOT NULL PRIMARY KEY,
    StudentName varchar(100) NOT NULL,
    ClassName varchar(100) NOT NULL,
    Marks int NOT NULL
);

INSERT INTO #StudentClass (ID, StudentName, ClassName, Marks)
VALUES (1, 'Roger',  'Science', 50),
       (2, 'Sara',   'Science', 40),
       (3, 'Jimmy',  'Science', 30),
       (4, 'Mike',   'Maths',   50),
       (5, 'Mona',   'Maths',   30),
       (6, 'Ronnie', 'Maths',   10),
       (7, 'James',  'Art',     50),
       (8, 'Mona',   'Art',     35),
       (9, 'Roger',  'Art',     25);

Partition the ranking by ClassName, then sort marks descending inside each partition. I added ID as a tie-breaker so an equal mark still produces a repeatable order when the rule is “exactly two rows.”

WITH Ranked AS
(
    SELECT ID, StudentName, ClassName, Marks,
           ROW_NUMBER() OVER
           (PARTITION BY ClassName ORDER BY Marks DESC, ID ASC)
               AS StRank
    FROM #StudentClass
)
SELECT StRank, ID, StudentName, ClassName, Marks
FROM Ranked
WHERE StRank <= 2
ORDER BY ClassName, StRank;

The result shows exactly two ranked rows for each of the three classes.

If the business rule is “include everyone tied for second,” ROW_NUMBER() is the wrong ranking rule: it always assigns separate row numbers. Consider RANK() or DENSE_RANK() and decide whether gaps after ties are acceptable.

For more ranking practice, see my earlier examples on RANK, DENSE_RANK and ROW_NUMBER, a broader ranking-functions example, and finding the Nth-highest row. For related variations, see PERCENT_RANK and returning top and bottom rows together. The interview answer starts by separating “top overall” from “top within each group.”

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.