SQL
Retrieving Top N Rows Per Group Using Window Functions
Discover how to efficiently fetch the top N highest or lowest ranked items within distinct groups using SQL window functions like ROW_NUMBER(), ideal for leaderboards or segmented reports.
SELECT product_id, category, sales_amount, rank_in_category
FROM (
SELECT
product_id,
category,
sales_amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) as rank_in_category
FROM
sales_data
) AS ranked_sales
WHERE rank_in_category <= 3;
How it works: This query uses a window function (`ROW_NUMBER()`) to assign a rank to each product within its respective category based on `sales_amount` in descending order. The `PARTITION BY category` clause ensures that ranking restarts for each category. The outer query then filters these results to only include products that are among the top 3 (`rank_in_category <= 3`) within their category, effectively retrieving the top N rows per group. This technique is more flexible and powerful than subqueries for complex ranking scenarios.