Lesson 12.6 · Reading time: ~7 min

Top N per group (also called "greatest N per group") is the task of picking the best few rows in every group: the three biggest customers of each store, the two longest films of each category, the latest order of each client. It comes up constantly in reports and in SQL interviews. In this lesson you'll solve it with ROW_NUMBER() and PARTITION BY, see how ties change the answer, and learn two alternatives: a correlated subquery and a LATERAL join.

Top N Rows per Group in SQL

ORDER BY ... LIMIT 3 returns the top three rows of the whole result, not of each group. Grouping doesn't help either: GROUP BY collapses each group to one row, while we need several original rows per group. The solution is to number the rows inside each group and keep the first N.

The examples run on the Sakila sample database (MySQL 8.0).

The Task

Find the three customers who paid the most in each of the two stores.

First, the total for each customer:

SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
       SUM(p.amount) AS total_paid
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
ORDER BY total_paid DESC
LIMIT 3;

Result: KARL SEAL (store 2, 221.55), ELEANOR HUNT (store 1, 216.54), CLARA SHAW (store 1, 195.58): the top three overall, not per store.

Solution 1: ROW_NUMBER with PARTITION BY

PARTITION BY store_id restarts the numbering for each store; ORDER BY total_paid DESC gives number 1 to the biggest payer. A window function can't be used in WHERE of the same query, so the filter goes into the outer query:

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
           SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
),
ranked AS (
    SELECT store_id, first_name, last_name, total_paid,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS rn
    FROM customer_total
)
SELECT store_id, first_name, last_name, total_paid, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;

Result: 6 rows, three per store.

Ties: ROW_NUMBER, RANK or DENSE_RANK

RHONDA KENNEDY and MARION SNYDER paid exactly the same, but ROW_NUMBER gave them different numbers. With ties, the three functions answer different questions. Let's look at the longest films of two categories, where lengths repeat often:

WITH ranked AS (
    SELECT c.name AS category, f.title, f.length,
           ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS row_num,
           RANK()       OVER (PARTITION BY c.name ORDER BY f.length DESC) AS rnk,
           DENSE_RANK() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS dense_rnk
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
)
SELECT category, title, length, row_num, rnk, dense_rnk
FROM ranked
WHERE category IN ('Action', 'Comedy') AND dense_rnk <= 3
ORDER BY category, row_num;

Result: 9 rows; in Action, two films share the top length of 185 minutes.

• ROW_NUMBER() <= N: exactly N rows per group; among equal values, the choice is arbitrary. Add a tie-breaker to ORDER BY, for example f.length DESC, f.title, to make it stable.
• RANK() <= N: everyone who would place in the top N, so a group can return more than N rows.
• DENSE_RANK() <= N: rows with the N highest distinct values.

In an interview, ask which one is meant: "top 3 films" is usually ROW_NUMBER, "films with the 3 longest durations" is DENSE_RANK.

Solution 2: Correlated Subquery

Without window functions (MySQL before 8.0), a row is in the top N of its group if fewer than N rows of the same group are greater:

SELECT c.name AS category, f.title, f.length
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
  AND (
      SELECT COUNT(*)
      FROM film f2
      JOIN film_category fc2 ON fc2.film_id = f2.film_id
      WHERE fc2.category_id = fc.category_id
        AND f2.length > f.length
  ) < 2
ORDER BY f.length DESC;

Result: 2 rows, DARN FORRESTER and WORST BANGER, the two films with no longer film in Action; this behaves like RANK() <= 2.

The subquery runs for each row, so this is slower on large tables, but it works in any database.

Solution 3: LATERAL Join

A LATERAL subquery can refer to columns of the tables before it in FROM. That lets you write "for each category, take its 2 longest films" literally, with ORDER BY and LIMIT inside:

SELECT c.name AS category, top_films.title, top_films.length
FROM category c
JOIN LATERAL (
    SELECT f.title, f.length
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    WHERE fc.category_id = c.category_id
    ORDER BY f.length DESC, f.title
    LIMIT 2
) AS top_films ON TRUE
WHERE c.name IN ('Action', 'Comedy')
ORDER BY category, top_films.length DESC;

Result: 4 rows: DARN FORRESTER and WORST BANGER for Action, CONTROL ANTHEM and SATURN NAME for Comedy.

LATERAL is supported by PostgreSQL and MySQL 8.0.14+, but not by MariaDB; SQL Server uses CROSS APPLY for the same idea. With an index on the grouping and sorting columns, this is often the fastest way when groups are large and N is small.

Practice

• Top Pickup Zones by Month
• Top-Paid Employees by Department
• Top Film Ratings by Popularity

Key Takeaways:

• LIMIT and GROUP BY can't return several rows per group; number the rows inside each group instead.
• ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) and WHERE rn <= N in an outer query is the standard solution.
• With ties, ROW_NUMBER returns exactly N rows, RANK may return more, and DENSE_RANK returns the N highest distinct values.
• A correlated subquery solves the task without window functions, but is slower.
• JOIN LATERAL (or CROSS APPLY in SQL Server) with ORDER BY ... LIMIT N is a readable and often fast alternative.

Frequently Asked Questions

How do I select the top N rows per group in SQL?

Number the rows of each group with ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) in a CTE or subquery, then keep the rows with a number up to N in the outer query.

Why can't I use the window function in WHERE?

Window functions are calculated after WHERE, GROUP BY and HAVING, during SELECT. To filter on their result, calculate them in a CTE or a subquery and filter in the outer query. Some databases (Snowflake, DuckDB, BigQuery) have a QUALIFY clause for this.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK for top N?

ROW_NUMBER gives every row a unique number, so it returns exactly N rows and breaks ties arbitrarily. RANK gives equal values the same rank and skips the next numbers, so it can return more than N rows. DENSE_RANK doesn't skip numbers and returns all rows with the N highest distinct values.

How do I get the latest row for each customer?

It is top 1 per group: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) and WHERE rn = 1. In PostgreSQL, SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC is a shorter alternative.