SQL Server Interview Question #95

How do you find the top N rows per group?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

A common solution is to calculate ROW_NUMBER or RANK partitioned by the grouping key and then filter the generated ranking in an outer query or CTE.

For example, to return the three most expensive products from every category, partition by CategoryId and order each partition by Price descending.

Code Example

WITH RankedProducts AS
(
    SELECT Id,
           Name,
           CategoryId,
           Price,
           ROW_NUMBER() OVER
           (
               PARTITION BY CategoryId
               ORDER BY Price DESC, Id
           ) AS rn
    FROM dbo.Products
)
SELECT Id, Name, CategoryId, Price
FROM RankedProducts
WHERE rn <= 3;