Top-N results per group

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Choose the two highest-priced products in every category.

Run the example

WITH Ranked AS (
    SELECT ProductID, CategoryID, ListPrice,
           ROW_NUMBER() OVER (PARTITION BY CategoryID
               ORDER BY ListPrice DESC, ProductID) AS rn
    FROM dbo.Products
)
SELECT CategoryID, ProductID, ListPrice, rn
FROM Ranked WHERE rn <= 2
ORDER BY CategoryID, rn;

Read it step by step

1. ROW_NUMBER partitions products by CategoryID and sorts each group by descending price.
2. ProductID breaks price ties deterministically.
3. The outer query keeps ranks 1 and 2 in each category, then orders the final result.

Check the result

Categories with fewer than two products contribute fewer than two rows.

Practice

Show the two highest-paid employees per DepartmentID, breaking salary ties by EmployeeID.

WITH Ranked AS (
    SELECT EmployeeID, DepartmentID, AnnualSalary,
           ROW_NUMBER() OVER (PARTITION BY DepartmentID
               ORDER BY AnnualSalary DESC, EmployeeID) AS rn
    FROM dbo.Employees
)
SELECT DepartmentID, EmployeeID, AnnualSalary, rn
FROM Ranked WHERE rn <= 2
ORDER BY DepartmentID, rn;