Top-N per group in SQL: matching the query to your data

Sooner or later, almost every application needs one row per group: the latest order for each customer, the most recent reading from each sensor, the current price of each product. It looks like a small query. In practice, the same result can cost very different amounts of work, because the best execution strategy depends less on the SQL you write than on the shape of your data.

This article walks through the main patterns in SQL Server and PostgreSQL, shows how EF Core and Spark express the same idea, and explains how to read the execution plan to confirm you picked the right one.

The example: latest order per customer

Take an orders table with customer_id, created_at, amount and status. We want the most recent order for each customer.

The most direct approach is GROUP BY customer_id with MAX(created_at), joined back to the table. It works, but it reads the table twice. It also returns duplicates when two orders share the same timestamp. The same problem exists in pandas, in Spark and in every ORM. What changes between them is how efficiently each engine can answer it.

Why data distribution decides the plan

Every pattern in this article follows one of two strategies. The first reads all the rows once, in group order, and keeps the top row of each group. Window functions and DISTINCT ON work this way. The second walks the list of groups and, for each one, seeks straight to its top row through an index. CROSS APPLY and LATERAL work this way.

Which strategy wins depends on the ratio of groups to rows. When a table holds 10 million rows spread across 200 customers, anything that reads every row loses to 200 index seeks. When the same 10 million rows belong to 5 million customers, those seeks become 5 million small lookups, and a single sorted scan wins. My rule of thumb:

• Many groups with few rows each: a window function (ROW_NUMBER()) or PostgreSQL's DISTINCT ON. One pass over the data is hard to beat.
• Few groups with many rows each: an index seek per group, using CROSS APPLY (SELECT TOP 1 ...) in SQL Server or LATERAL ... LIMIT 1 in PostgreSQL, backed by an index on (group_key, sort_column DESC).
• Few groups, and no separate table that lists them: in PostgreSQL, a recursive CTE that emulates a loose index scan.

In every case, confirm the choice with the execution plan, not the query text.

SQL Server

ROW_NUMBER in a CTE

WITH ranked AS (
    SELECT o.customer_id, o.created_at, o.amount, o.status,
           ROW_NUMBER() OVER (PARTITION BY o.customer_id
                              ORDER BY o.created_at DESC, o.order_id DESC) AS rn
    FROM dbo.orders AS o
)
SELECT customer_id, created_at, amount, status
FROM ranked
WHERE rn = 1;

Add a tiebreaker such as order_id to the ORDER BY. Without one, the result is nondeterministic whenever two orders tie on the timestamp. In the plan, look for Segment, Sequence Project and Filter. If there is also a Sort operator, there is no index delivering rows in (customer_id, created_at DESC) order. On a large table, that sort and its memory grant usually account for most of the cost.

CROSS APPLY with TOP 1

CREATE INDEX IX_orders_customer_created
    ON dbo.orders (customer_id, created_at DESC)
    INCLUDE (amount, status);

SELECT c.customer_id, x.created_at, x.amount, x.status
FROM dbo.customers AS c
CROSS APPLY (
    SELECT TOP (1) o.created_at, o.amount, o.status
    FROM dbo.orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.created_at DESC, o.order_id DESC
) AS x;

The plan you want is Nested Loops with an Index Seek and Top on the inner side. Each customer then costs a few logical reads, no matter how many orders it has. Use OUTER APPLY if you also want customers who have no orders. The INCLUDE columns matter: without them, every seek also does a Key Lookup, and the advantage shrinks quickly. This pattern needs a driving table of groups, here customers. If you have to build that list with SELECT DISTINCT customer_id FROM orders, you have already scanned the table once.

TOP 1 WITH TIES

SELECT TOP (1) WITH TIES customer_id, created_at, amount, status
FROM dbo.orders
ORDER BY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC);

It's short and it's correct, but it's rarely the fastest option. The engine has to compute the row number for every row and then sort on it. I'd use it in an ad-hoc query, not in production code.

PostgreSQL

DISTINCT ON

SELECT DISTINCT ON (customer_id)
       customer_id, created_at, amount, status
FROM orders
ORDER BY customer_id, created_at DESC, order_id DESC;

This is PostgreSQL-specific and very readable. The leading ORDER BY expressions must match the DISTINCT ON expressions. It reads every row, so with an index on (customer_id, created_at DESC) you get an ordered index scan with Unique on top. Without that index you get a Sort. It is the PostgreSQL equivalent of the ROW_NUMBER approach: excellent for many small groups, wasteful for a few very large ones.

LATERAL

SELECT c.customer_id, o.created_at, o.amount, o.status
FROM customers AS c
CROSS JOIN LATERAL (
    SELECT created_at, amount, status
    FROM orders
    WHERE orders.customer_id = c.customer_id
    ORDER BY created_at DESC, order_id DESC
    LIMIT 1
) AS o;

This is the counterpart of CROSS APPLY. Use LEFT JOIN LATERAL ... ON true to keep customers with no orders. With INCLUDE (amount, status) on the index, you can also get Index Only Scan on the inner side, provided the visibility map is mostly up to date.

A recursive CTE as a loose index scan

When there are only a few distinct groups and no table that lists them, you can emulate a loose index scan. The CTE jumps from one key to the next through the index:

WITH RECURSIVE keys AS (
    (SELECT customer_id FROM orders ORDER BY customer_id LIMIT 1)
    UNION ALL
    SELECT (SELECT o.customer_id
            FROM orders AS o
            WHERE o.customer_id > k.customer_id
            ORDER BY o.customer_id
            LIMIT 1)
    FROM keys AS k
    WHERE k.customer_id IS NOT NULL
)
SELECT x.*
FROM keys AS k
CROSS JOIN LATERAL (
    SELECT customer_id, created_at, amount, status
    FROM orders
    WHERE orders.customer_id = k.customer_id
    ORDER BY created_at DESC, order_id DESC
    LIMIT 1
) AS x
WHERE k.customer_id IS NOT NULL;

It's ugly, but with 200 customers it means about 400 small index probes instead of reading 10 million rows.

PostgreSQL 18 skip scan and its scope

PostgreSQL 18 shipped on 2025-09-25 with B-tree skip scan. According to the release notes, it lets multicolumn B-tree indexes be used when the leading columns have no restriction, or only non-equality ones, but a later column does have a useful restriction. That helps a query like WHERE created_at >= now() - interval '1 day' on an index that leads with customer_id. The release notes don't present it as a DISTINCT ON or GROUP BY optimization, so I wouldn't count on it replacing the recursive CTE. Run EXPLAIN on your version and check.

EF Core

var latest = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => g.OrderByDescending(o => o.CreatedAt)
                  .ThenByDescending(o => o.OrderId)
                  .First())
    .ToListAsync();

EF Core 6.0 added translation for GroupBy followed by First/FirstOrDefault over the group, and for top-N per group with Take. Earlier versions threw an exception or evaluated on the client. On SQL Server, the generated SQL typically uses ROW_NUMBER() OVER (PARTITION BY ...) with a row <= 1 filter, which means you get the window-function shape whether or not it suits your data. If you need the APPLY shape, start from the parent set:

var latest = await db.Customers
    .Select(c => c.Orders
                  .OrderByDescending(o => o.CreatedAt)
                  .FirstOrDefault())
    .ToListAsync();

Either way, log the SQL with ToQueryString() or LogTo and look at the plan. Don't assume.

Spark

from pyspark.sql import Window, functions as F

w = Window.partitionBy("customer_id").orderBy(F.col("created_at").desc(), F.col("order_id").desc())

latest = (orders
          .withColumn("rn", F.row_number().over(w))
          .where(F.col("rn") == 1)
          .drop("rn"))

The window approach shuffles by customer_id and sorts within each partition. If you only need a few columns from the winning row, max_by is a better fit. It's an aggregate, so it doesn't need to sort rows within each group:

latest = (orders
          .groupBy("customer_id")
          .agg(F.max_by(F.struct("created_at", "amount", "status"), "created_at").alias("o"))
          .select("customer_id", "o.*"))

max_by/min_by have been available as SQL functions since Spark 3.0, and as PySpark DataFrame functions since 3.3.0. On ties they are nondeterministic, so put a tiebreaker in the ordering expression if that matters. Don't use dropDuplicates after orderBy: Spark doesn't guarantee which row survives.

Pitfalls

• No tiebreaker. Two rows with the same timestamp produce results that change from run to run. Always add a unique column at the end of the ORDER BY.
• Wrong index direction or missing INCLUDE columns. Without them, seeks turn into scans or into key lookups for every row.
• Reading the query instead of the plan. In SQL Server, compare SET STATISTICS IO ON logical reads. In PostgreSQL, use EXPLAIN (ANALYZE, BUFFERS) and check loops multiplied by rows on the inner side of a nested loop.
• Skewed data. If 90% of your rows belong to five customers, test with production-shaped data, not a uniform test set.

Summary and checklist

Selecting one row per group is a question of strategy before it is a question of syntax. Scan-based patterns win when there are many small groups, seek-based patterns win when there are few large ones, and the right index makes either of them cheap. ORMs and Spark follow the same rules, so check what they generate.

1. Count the groups and the rows per group before choosing a pattern.
2. Many small groups: ROW_NUMBER() or DISTINCT ON. Few large groups: APPLY/LATERAL with TOP 1/LIMIT 1.
3. Create an index on (group_key, sort_column DESC) and include the columns you return.
4. Always add a deterministic tiebreaker.
5. Check the SQL your ORM generates, and read the plan, not the SQL text.