Top N Rows per Group in SQL Server: ROW_NUMBER, DENSE_RANK and CROSS APPLY

“The three highest-paid employees in each department”, “each customer’s last five orders”, “the ten best-selling products in each product group”… These are versions of the same problem: top N rows per group. There are several ways to solve it in T-SQL, and they don’t all return the same result. How ties (two employees with the same salary) are handled, which index the query uses and how many reads it does on large tables are the questions that decide.

This article compares three methods on an example, along with how they treat ties and how they perform.

Sample Data

CREATE TABLE dbo.Department (
    DepartmentId   int PRIMARY KEY,
    DepartmentName nvarchar(50) NOT NULL
);

CREATE TABLE dbo.Employee (
    EmployeeId   int PRIMARY KEY,
    FullName     nvarchar(100) NOT NULL,
    DepartmentId int NOT NULL REFERENCES dbo.Department (DepartmentId),
    Salary       decimal(19, 2) NOT NULL
);

The goal: list the three highest-paid employees in each department, together with the department name.

Method 1: ROW_NUMBER

WITH ranked AS (
    SELECT e.EmployeeId, e.FullName, e.DepartmentId, e.Salary,
           ROW_NUMBER() OVER (PARTITION BY e.DepartmentId
                              ORDER BY e.Salary DESC, e.EmployeeId) AS rn
    FROM dbo.Employee AS e
)
SELECT d.DepartmentName, r.FullName, r.Salary, r.rn
FROM ranked AS r
JOIN dbo.Department AS d ON d.DepartmentId = r.DepartmentId
WHERE r.rn <= 3
ORDER BY d.DepartmentName, r.rn;

Exactly three rows come back per department. If employees share a salary, the second column in the ORDER BY (EmployeeId) decides which one makes the list. Without it, the result can change from run to run.

Method 2: Including Ties with DENSE_RANK

If you want “everyone at the top three salary levels”, use a ranking function that includes ties:

WITH ranked AS (
    SELECT e.FullName, e.DepartmentId, e.Salary,
           DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS rnk
    FROM dbo.Employee AS e
)
SELECT d.DepartmentName, r.FullName, r.Salary, r.rnk
FROM ranked AS r
JOIN dbo.Department AS d ON d.DepartmentId = r.DepartmentId
WHERE r.rnk <= 3
ORDER BY d.DepartmentName, r.rnk;

Method 3: CROSS APPLY with TOP

When there are few departments and many employees per department, running a separate TOP query per department can return the result with far fewer reads:

SELECT d.DepartmentName, t.FullName, t.Salary
FROM dbo.Department AS d
CROSS APPLY (
    SELECT TOP (3) e.FullName, e.Salary
    FROM dbo.Employee AS e
    WHERE e.DepartmentId = d.DepartmentId
    ORDER BY e.Salary DESC, e.EmployeeId
) AS t
ORDER BY d.DepartmentName, t.Salary DESC;

To include ties, use TOP (3) WITH TIES; in that case the ORDER BY should be only Salary DESC.

The Key to Performance: The Right Index

All three methods benefit from the same index: the grouping column first, the sort column second, and the returned columns in INCLUDE.

CREATE NONCLUSTERED INDEX IX_Employee_Department_Salary
ON dbo.Employee (DepartmentId, Salary DESC)
INCLUDE (FullName);

• ROW_NUMBER and DENSE_RANK: thanks to the index, no separate sort is needed, but every row of the table is still read and numbered.
• CROSS APPLY + TOP: for each department, goes straight to the relevant spot in the index and reads only three rows. With few departments, it reads far less; with very many departments, the advantage shrinks.

Summary

• Exactly N rows per group: ROW_NUMBER, with the ORDER BY ending in a unique column.
• Ties included: DENSE_RANK or TOP … WITH TIES.
• Few groups, many rows per group: CROSS APPLY + TOP usually reads the least.
• In every case: an index suited to the grouping and sort columns.