Baldwin Apps

Posted on Jul 14

SQL Pattern Series #14: The Top-Per-Group Pattern

#beginners #tutorial #database #sql

Finding the most important row within each group

SQL Pattern Series #14 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to find the top row within each group
Why ROW_NUMBER() is commonly used for this problem
How partitioning creates independent rankings
When to use the Top-Per-Group Pattern

Many SQL problems involve finding a single "best" row.

For example:

The latest order for each customer
The highest sale in each region
The newest status update for each ticket
The most recent login for each user

The challenge is that you don't want the top row overall.

You want the top row within each group.

That is where the Top-Per-Group Pattern becomes useful.

The Problem

Imagine an Orders table:

CustomerID	OrderDate
1	2024-01-15
1	2024-02-10
2	2024-01-20
2	2024-03-01

Suppose you need:

The most recent order for each customer.

A simple ORDER BY won't solve the problem.

You need to rank rows separately within each customer group.

The Top-Per-Group Pattern

The most common solution uses:

ROW_NUMBER() OVER (
    PARTITION BY column
    ORDER BY column DESC
)

The window function creates a ranking inside each group.

The highest-ranked row receives:

rn = 1

Those are the rows we keep.

Example

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.CustomerID
            ORDER BY o.OrderDate DESC
        ) AS rn
    FROM Orders o
) t
WHERE rn = 1;

This query:

Groups rows by customer.
Orders each customer's rows from newest to oldest.
Assigns row numbers.
Returns only the first row in each group.

Example Result

CustomerID	OrderDate
1	2024-02-10
2	2024-03-01

Each customer now has exactly one row.

The most recent one.

Why This Pattern Matters

Many reporting and analytics tasks require a single representative row.

Examples include:

latest customer activity
newest support ticket update
highest scoring transaction
most recent account balance
top-performing product per category

The Top-Per-Group Pattern solves these problems consistently.

Partitioning Creates Independent Rankings

The key idea is:

PARTITION BY CustomerID

Without partitioning:

ROW_NUMBER()

would rank all rows together.

With partitioning:

ROW_NUMBER()
OVER (
    PARTITION BY CustomerID
)

each customer receives their own ranking sequence.

Think of it as creating a separate leaderboard for each group.

A Note on Ties

Suppose two orders have the exact same timestamp.

Which row should become:

rn = 1

Without a tie-breaker, the database may choose one arbitrarily.

For deterministic results, consider adding another column:

ROW_NUMBER() OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate DESC,
             OrderID DESC
)

This guarantees a predictable winner.

ROW_NUMBER vs RANK

The Top-Per-Group Pattern typically uses:

ROW_NUMBER()

because it guarantees exactly one row receives:

rn = 1

Functions such as:

RANK()

or:

DENSE_RANK()

may return multiple rows when ties occur.

That behavior can be useful, but it solves a slightly different problem.

When I Reach for This Pattern

I typically use the Top-Per-Group Pattern when:

I need the latest row per entity
I need the highest value per category
I need one representative row from each group
ranking matters within groups
duplicate historical records exist

Examples include:

latest order per customer
newest employee record
highest sale per region
most recent account activity
latest status change per ticket

Key Takeaway

Many SQL problems are really asking:

Which row wins within each group?

The Top-Per-Group Pattern answers that question.

Create a ranking.

Partition by the group.

Keep:

rn = 1

Sometimes finding the best row is easier than aggregating them all.