Top N per Group: Three Ways and Which Is Fastest

Asking for the latest three orders per customer sounds simple until ties and uneven customer sizes appear. A top N per group query needs both a selection rule and a plan that fits the real data.

Define Latest and the Required Population

Decide whether the result needs exactly a maximum of three rows, every row tied at the third position, or the three latest distinct dates. Those are different contracts. Also decide whether customers without orders should appear. A query cannot choose those rules correctly merely because its function name includes rank or top.

I ask about ties before comparing plans. Otherwise the fastest-looking statement can be solving an easier or different problem. Define a stable unique ordering key when the result should choose exactly three rows. For orders, date followed by a unique OrderID gives deterministic selection when dates match.

The examples use a small synthetic dataset with two populated customers and one empty customer. The inputs explain semantics, not measured performance. Rehearse with representative customer skew and table size before accepting a claim about the fastest production pattern.

CREATE TABLE #Customers (CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE #Orders
(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate datetime2 NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #Customers VALUES (1),(2),(3);
INSERT #Orders VALUES
(1,1,'2026-09-20',10),(2,1,'2026-09-21',20),
(3,1,'2026-09-21',30),(4,1,'2026-09-22',40),
(5,1,'2026-09-22',50),(6,2,'2026-09-19',15),
(7,2,'2026-09-23',25),(8,2,'2026-09-24',35);

Top N per Group With ROW_NUMBER

ROW_NUMBER partitions the ordering by customer and assigns a distinct sequence number to each row. A CTE makes the calculated number available to an outer filter. The unique secondary order ensures that a date tie does not make the chosen identities depend on an unspecified ordering.

DECLARE @N int=3;
WITH Ranked AS
(
    SELECT OrderID,CustomerID,OrderDate,Amount,
           ROW_NUMBER() OVER
           (PARTITION BY CustomerID ORDER BY OrderDate DESC,OrderID DESC) AS PositionInGroup
    FROM #Orders
)
SELECT OrderID,CustomerID,OrderDate,Amount
FROM Ranked WHERE PositionInGroup<=@N
ORDER BY CustomerID,OrderDate DESC,OrderID DESC;

This pattern is straightforward for a broad report across many customers. Its plan can process a large input and sort if no useful ordering access path exists. The CTE is a query expression, not a promise to materialize an intermediate table. Inspect the actual physical operators and row processing.

Top N per Group With CROSS APPLY

CROSS APPLY runs a correlated table expression for each chosen customer. TOP limits that customer's ordered result. The explicit customer table lets you restrict the outer population before retrieving orders, which can be useful when a screen requests a small known set of customers.

DECLARE @N int=3;
SELECT c.CustomerID,o.OrderID,o.OrderDate,o.Amount
FROM #Customers AS c
CROSS APPLY
(
    SELECT TOP (@N) OrderID,OrderDate,Amount
    FROM #Orders AS x
    WHERE x.CustomerID=c.CustomerID
    ORDER BY OrderDate DESC,OrderID DESC
) AS o
ORDER BY c.CustomerID,o.OrderDate DESC,o.OrderID DESC;

CROSS APPLY excludes a customer whose inner query returns nothing. Use OUTER APPLY when the contract requires that customer with NULL order attributes. That preserves the customer, not three invented order rows. Keep the absent-order representation clear to the application consumer.

With a suitable leading customer key, the plan can seek to a customer's ordered rows and stop after its requested population. Without that path, repeated searches can become expensive. APPLY describes the logical relationship; it does not itself guarantee a cheap lookup for every outer row.

Top N per Group With a Correlated Count

A correlated subquery can count rows that precede the current order under the same descending order rule. Keep rows with fewer than N newer rows. Include the unique secondary key in the comparison so the query implements the same exact-row contract as the earlier two statements.

DECLARE @N int=3;
SELECT o.OrderID,o.CustomerID,o.OrderDate,o.Amount
FROM #Orders AS o
WHERE
(
    SELECT COUNT_BIG(*) FROM #Orders AS newer
    WHERE newer.CustomerID=o.CustomerID
      AND (newer.OrderDate>o.OrderDate
           OR (newer.OrderDate=o.OrderDate AND newer.OrderID>o.OrderID))
)<@N
ORDER BY o.CustomerID,o.OrderDate DESC,o.OrderID DESC;

This is useful for explaining rank without a window function. Its repeated counting can require substantial work on larger groups, although the optimizer can transform parts of the plan. Do not call it universally slow from its syntax alone. Compare the actual operators, reads, and execution behavior against equivalent results on representative inputs. On the sample data, all three exact-row statements return the same six rows, and customer 3 does not appear.

Choose the Intended Tie Behavior

RANK assigns the same position to tied ordering values and leaves gaps after those ties. DENSE_RANK also shares positions but counts successive distinct values without gaps. For the three latest distinct order dates, use dense rank on the date only. Including OrderID would eliminate the very ties the rule is meant to preserve.

WITH Ranked AS
(
    SELECT *,RANK() OVER
        (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowPositionRank,
        DENSE_RANK() OVER
        (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS DateGroupRank
    FROM #Orders
)
SELECT OrderID,CustomerID,OrderDate,RowPositionRank,DateGroupRank
FROM Ranked WHERE DateGroupRank<=3;

TOP WITH TIES instead includes all rows tied at the cutoff ordering value. Apply it inside the customer lookup and order by date alone for date ties. Add a deterministic outer display order separately. A secondary unique key inside the TOP ordering would prevent ties and change the contract. On the sample data, the next query returns four rows for customer 1 because orders 2 and 3 share the cutoff date.

SELECT c.CustomerID,o.OrderID,o.OrderDate
FROM #Customers AS c
CROSS APPLY
(
    SELECT TOP (3) WITH TIES OrderID,OrderDate
    FROM #Orders AS x WHERE x.CustomerID=c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID,o.OrderDate DESC,o.OrderID DESC;

Support the Customer and Ordering Keys

An index beginning with CustomerID, then descending OrderDate and OrderID, supports the partition and ordered lookup used by the exact-row examples. Including Amount can avoid a separate payload lookup for this selected output. Additional included columns need their own write and storage justification.

CREATE INDEX IX_Orders_CustomerRecent
ON #Orders(CustomerID,OrderDate DESC,OrderID DESC)
INCLUDE (Amount);

The index gives each pattern a useful candidate access path, but does not make their work identical. The numbering pattern can process broad ordered input, while APPLY can exploit a selective outer population. The counting expression can still examine more candidate relationships. An index is an opportunity for the optimizer, not an instruction to declare every plan equivalent.

Measure Equivalent Plans on Representative Data

Enable actual plans and STATISTICS IO before rerunning the three exact-row statements. Compare the same selected customers, N value, projected columns, and tie rule. Record the data population and execution conditions so the measurements are repeatable. No timing or read count is promised by this small article sample. A stopwatch is indifferent to the query style we prefer.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Run the three equivalent exact-row statements under the accepted test conditions.
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

I compare skewed groups and the application's usual customer selection, not just an evenly distributed test. A broad report and a single-customer lookup can favor different plans. Watch sort spills, repeated seeks, memory grants, and total reads alongside elapsed time. The most elegant query text is not a substitute for that evidence.

Keep Semantics Ahead of the Winner Label

Which result should a customer with tied dates receive? Confirm the expected identities before ranking execution speed. Top N per group is only an equivalent comparison when all candidate statements answer the same question. A statement returning fewer required rows has not earned a performance victory.

Choose the clear pattern whose verified plan meets the workload objective, and retain the supporting index review. Top N per group then becomes a documented query contract with measured behavior, rather than a permanent claim that one syntax is fastest everywhere.

Related reading on this blog: How to Get Top N Records Per Group? Ranking Function: Interview Question of the Week #156 and CROSS APPLY and OUTER APPLY in Everyday Queries.

The fastest pattern is not a universal syntax choice, it is the verified plan for the same result contract and representative workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.