SQL
Retrieve Nth Row Per Group Using Window Functions
Discover how to efficiently fetch the Nth highest or lowest record for each group using SQL window functions like ROW_NUMBER(), ideal for scenarios like 'top N products per category'.
WITH RankedProducts AS (
SELECT
category_id,
product_name,
price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rn
FROM products
)
SELECT category_id, product_name, price
FROM RankedProducts
WHERE rn = 1;
How it works: This query uses a Common Table Expression (CTE) and the `ROW_NUMBER()` window function to find the product with the highest price within each category. `PARTITION BY category_id` divides the data into groups, and `ORDER BY price DESC` assigns a rank (row number) within each group based on price. By selecting `WHERE rn = 1`, we effectively get the highest-priced product for every category. Changing `rn = 1` to `rn = 2` would retrieve the second highest, and so on.