SQL
Find Nth Value Per Group Using Window Functions
Master SQL window functions like RANK() or ROW_NUMBER() to efficiently find the Nth largest or smallest value within each group, a powerful technique for data analysis.
SELECT category, product_name, price
FROM (
SELECT
category,
product_name,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rn
FROM products
) AS ranked_products
WHERE rn = 1; -- Change '1' to '2' for second largest, etc.
How it works: This SQL query utilizes the `ROW_NUMBER()` window function to find the top (or Nth) product by price within each `category`. `PARTITION BY category` divides the dataset into independent groups, and `ORDER BY price DESC` ranks products within each category from highest to lowest price. The outer query then filters to retrieve only the product with the desired rank (e.g., `rn = 1` for the most expensive).