SQL Server Top N Rows per Group with ROW_NUMBER()

To return the top N rows inside every group in SQL Server, assign a row number within each group and filter it in an outer query. The PARTITION BY clause restarts numbering for each group; the ORDER BY inside OVER decides which rows rank first.

Return the three newest sales per customer

Assume dbo.Sales has a unique sale_id and stores each sale’s customer, date, and amount. Use the unique ID as a tie-breaker so the result is repeatable when two sales have the same date:

DECLARE @TopN int = 3;

WITH RankedSales AS (
  SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY sale_date DESC, sale_id DESC
    ) AS row_num
  FROM dbo.Sales
)
SELECT sale_id, customer_id, sale_date, amount
FROM RankedSales
WHERE row_num <= @TopN
ORDER BY customer_id, row_num;

ROW_NUMBER() starts at 1 in each customer partition. The CTE calculates the rankings; the outer query keeps only the first @TopN rows from each one. Microsoft notes that row numbering is nondeterministic unless the partition and ordering columns uniquely identify each row, which is why this example adds sale_id to the ordering. See the ROW_NUMBER() reference.

The ORDER BY inside OVER controls rank assignment, not the final output order. Keep the outer ORDER BY when the returned rows also need to be presented by customer and rank.

Include ties at the cutoff

ROW_NUMBER() always assigns distinct numbers, so it returns no more than N rows per group. If ties at the Nth value should all be included, use RANK() and order by the value that defines a tie:

WITH RankedSales AS (
  SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    RANK() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC
    ) AS amount_rank
  FROM dbo.Sales
)
SELECT sale_id, customer_id, sale_date, amount
FROM RankedSales
WHERE amount_rank <= 3
ORDER BY customer_id, amount_rank, sale_id;

If several sales tie for third place, this query returns every tied sale and therefore can return more than three rows for a customer. RANK() leaves gaps after ties; use DENSE_RANK() when the top N distinct values are needed without rank gaps. See Microsoft’s ranking functions reference.

Add an index for large tables

When this query runs often on a large table, test an index whose leading columns match the group and sort keys:

CREATE INDEX IX_Sales_Customer_Date_Id
ON dbo.Sales (customer_id, sale_date DESC, sale_id DESC)
INCLUDE (amount);

This index may let SQL Server read rows in the required group and date order while covering the selected amount. Check the actual execution plan and workload before keeping the index, because each additional index also adds storage and write maintenance.

Common mistakes

• Using TOP (@TopN) once: that limits the whole result set, not each customer group. Put the ranking function in a partitioned window instead.
• Filtering ROW_NUMBER() in the same query’s WHERE: calculate it in a CTE or derived table, then filter the calculated column in the outer query.
• Omitting a unique tie-breaker: rows with identical sort values can receive row numbers in different orders across executions. Add a unique column to the window’s ORDER BY when exactly N repeatable rows are required.
• Expecting exactly N rows with RANK(): RANK() <= N includes ties and may return extra rows by design.

For the same task in MySQL, see Top N Rows per Group in MySQL. The SQL Server ROW_NUMBER() reference documents the function’s syntax and behavior.