TryBench
Data Analysis & SQL · Practitioner lesson 19 of 19

SQL ROW_NUMBER PARTITION BY: Top N Per Group

Use ROW_NUMBER with PARTITION BY to find the top record inside every group.

Why this matters

Questions like "the best order in each region" are common. Ranking inside groups answers them cleanly.

PARTITION BY Starts Over for Each Group

ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) numbers rows 1, 2, 3 inside each region. It starts again at 1 for the next region.

In real life: Think of a sports league with separate tables for each group. The best player in Group A is number 1, and Group B starts again at 1. PARTITION BY builds those separate tables.

SELECT region, order_id, amount,
  ROW_NUMBER() OVER (
    PARTITION BY region ORDER BY amount DESC
  ) AS rn
FROM orders;

Filter Ranks in a Second Query

You cannot use a window function in WHERE. Put the ranking in a CTE, then keep rn = 1 in the final SELECT.

In real life: The ranking has to exist before you can pick rank 1 from it. So you build the ranking first (in WITH), then filter it in the query after it.

ROW_NUMBER or RANK

ROW_NUMBER gives every row a different number. RANK gives tied rows the same number. Pick the one that fits how ties should work.

In real life: ROW_NUMBER gives every row a different number. RANK gives tied rows the same number. Add a tie-breaker like order_id so results do not change.

Your practice task

Each regional manager wants to see their single biggest completed order. Return region, order_id, and amount for the largest COMPLETED order in every region, sorted by region A to Z.

How to do it

  1. Rank the completed orders inside each region, biggest first.
  2. Decide what breaks a tie.
  3. Keep only rank number one in each region.
  4. Sort by region, then press Run proof.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: Top-N Per Group

Use rn <= 3 for the top three per group. This pattern powers leaderboards and "best of each" reports.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY group_col ORDER BY value DESC) AS rn
  FROM t
)
SELECT * FROM ranked WHERE rn = 1;

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL