SQL

Find Nth Highest Value Per Group Using Window Functions

Learn to retrieve the Nth highest or lowest value within distinct groups of data using SQL window functions like ROW_NUMBER() or RANK(), essential for analytical rankings.

WITH RankedProducts AS (
  SELECT
    product_id,
    product_name,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rn
  FROM products
)
SELECT product_id, product_name, category_id, price
FROM RankedProducts
WHERE rn = 1; -- Change 'rn' to 2 for 2nd most expensive, etc.

How it works: This query uses a Common Table Expression (CTE) with the `ROW_NUMBER()` window function. It partitions the products by `category_id` and orders them by `price` in descending order within each category. The `rn` column assigns a rank to each product. The outer query then filters to retrieve the product with `rn = 1`, effectively finding the most expensive product in each category. This pattern is easily adaptable to find the Nth highest/lowest by changing `rn`.

Find Nth Highest Value Per Group Using Window Functions

Need help integrating this into your project?