SQL
Find Nth Highest/Lowest Value Per Group Using Window Functions
Efficiently retrieve the Nth highest or lowest value for each group in SQL using powerful window functions like ROW_NUMBER(). A key technique for ranked data.
-- Example Table: products (product_id, category_id, product_name, price)
-- Find the 2nd most expensive product in each category
WITH RankedProducts AS (
SELECT
product_id,
category_id,
product_name,
price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rn
FROM
products
)
SELECT
product_id,
category_id,
product_name,
price
FROM
RankedProducts
WHERE
rn = 2;
-- To find the 2nd least expensive product, change ORDER BY price DESC to ORDER BY price ASC
How it works: This snippet demonstrates how to find the Nth highest or lowest value within distinct groups using SQL window functions. It first assigns a rank (row number) to each product within its `category_id`, ordered by `price` (descending for highest, ascending for lowest). A Common Table Expression (CTE) `RankedProducts` temporarily stores these ranked results, from which you can then easily select rows where the rank `rn` equals your desired N (e.g., `rn = 2` for the second most expensive).