Extracting Top-N Records per Group

Extracting Top-N Records per Group

In this lesson, you will learn how to isolate the "best" or "most significant" items within individual categories, such as finding the top 3 highest-paid employees in every department.

The Problem: Beyond Simple Aggregates

While standard aggregation (like GROUP BY and MAX()) can tell you the highest salary in a department, it fails when you need to retrieve the entire row associated with those top values—for instance, including the employee's name and hire date along with the salary.

To solve this, we use the ROW_NUMBER() window function. This allows us to assign a rank to every row within its specific group.

The Implementation Strategy

Imagine we have a sales table with category and amount columns. If we want the top 2 products for each category, we follow these three logical steps:

1. Partition: Divide the dataset into groups (e.g., by category).
2. Order: Within those groups, sort the rows based on the metric you care about (e.g., amount DESC).
3. Assign: Generate a rank number for each row.

WITH RankedSales AS (
  SELECT 
    product_name, 
    category, 
    amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) as rank
  FROM sales
)
SELECT * 
FROM RankedSales 
WHERE rank <= 2;

Why This Works

The PARTITION BY clause acts like a miniature GROUP BY that doesn't collapse your rows. It creates "windows" where the numbering resets for every unique category. Once the numbering is assigned, we wrap the logic in a Common Table Expression (CTE) or a subquery so we can filter by that rank column.

This approach is highly performant and flexible. If you later decide you need the "top 5" instead of the "top 2," you simply change the filter in the final WHERE clause without rewriting your entire aggregation logic.

Key Takeaway: Use the ROW_NUMBER() window function with PARTITION BY to rank items within categories, allowing you to extract any number of top-performing records per group.