Top-N-per-group queries return a limited number of highest or lowest records within each category or entity group.
Business users frequently need results such as the top three products per category or top five employees per department.
Find the top three highest-paid employees in every department.
WITH RankedEmployees AS (
SELECT
e.EmployeeName,
e.DepartmentID,
e.Salary,
ROW_NUMBER() OVER (
PARTITION BY e.DepartmentID
ORDER BY e.Salary DESC
) AS rn
FROM Employees e
)
SELECT
r.EmployeeName,
d.DepartmentName,
r.Salary
FROM RankedEmployees r
INNER JOIN Departments d
ON r.DepartmentID = d.DepartmentID
WHERE r.rn <= 3;Used in sales rankings, HR analytics, product analysis, and performance dashboards.
- PARTITION BY creates separate groups.
- ROW_NUMBER ranks rows within each group.
- Filtering the rank returns the required N records.
- JOIN can add descriptive group information.
- Tie handling should be considered.
How do you find the top three employees per department?
Use ROW_NUMBER partitioned by department and ordered by salary descending, then filter for row numbers 1 through 3.
Why not simply use TOP 3?
TOP 3 limits the entire result, while ROW_NUMBER can return the top three records separately for every group.