Ranking and Top-N per Group

Description

"Show the top 3 best-selling products in each category." It sounds like ORDER BY ... LIMIT 3, but that gives the top 3 overall, not per category. GROUP BY category gives one row per category and loses the products. What you need is a way to rank rows within each group while keeping every row - and that's what a window function does.

The same shape turns up again and again: "the second highest salary", "each customer's most recent order", "keep only the newest signup per email", "who's in the top 10% of sellers". They're among the most common SQL interview questions, and the answers usually hinge on one small detail - what should happen when two rows tie. In this lesson:

1. What a window function is, and how it differs from GROUP BY.
2. ROW_NUMBER, RANK and DENSE_RANK - three ways to handle ties.
3. Top N per group, and why you can't filter on a rank in WHERE.
4. The second highest value, the latest row per group, and removing duplicates.
5. Where NULLs land when you sort biggest-first.

Examples use eight employees (department, salary, hired_on) with two deliberate ties, five signups with duplicate emails, and three bonuses, one of them NULL. Every result was produced by PostgreSQL 18.

Windows keep the rows

A window function computes a value for each row from a set of related rows - its window - without collapsing them. OVER (...) defines the window:

• PARTITION BY dept - split rows into groups; the calculation restarts in each one.
• ORDER BY salary DESC - order rows inside each partition (needed for ranking and running totals).

SELECT name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department)) AS dept_avg
FROM employees
ORDER BY id;

Every aggregate (SUM, AVG, COUNT, ...) can be used this way. Each row keeps its own columns and gains its department's average - "compare with the group" without a join or a correlated subquery.

ROW_NUMBER, RANK and DENSE_RANK

They differ only on ties:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_number,
       RANK()       OVER (ORDER BY salary DESC)       AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC)       AS dense_rank
FROM employees
WHERE department = 'eng'
ORDER BY salary DESC, name;

• ROW_NUMBER() numbers rows 1, 2, 3, ... with no ties. Which of two tied rows comes first is arbitrary unless the ORDER BY breaks the tie (here, name).
• RANK() gives ties the same number and then skips (1, 2, 2, 4) - Olympic medals.
• DENSE_RANK() gives ties the same number without gaps (1, 2, 2, 3).

So "the top 2 salaries" means three different things. ROW_NUMBER() <= 2 returns exactly two people (dropping a tied one at random); RANK() <= 2 returns everyone ranked 1 or 2 (three people here); DENSE_RANK() <= 2 returns everyone earning one of the two highest distinct salaries. Read the requirement for its tie rule before choosing.

Which ranking function does the requirement want?

RANK and DENSE_RANK agree here; they part ways one step further down. Asked for the top 3, RANK still stops at the same three people (Noah ranks 4th), while DENSE_RANK adds Noah, because 120 is the third-highest distinct salary.

Filtering on a window: the top N per group

Window functions are computed after WHERE, GROUP BY and HAVING - just before ORDER BY - so WHERE DENSE_RANK() OVER (...) <= 2 is an error ("window functions are not allowed in WHERE"). Compute the rank in a derived table (or a CTE) and filter outside it:

SELECT department, name, salary
FROM (SELECT department, name, salary,
             DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
      FROM employees) AS ranked
WHERE r <= 2
ORDER BY department, salary DESC, name;

(Snowflake, BigQuery and DuckDB have a QUALIFY clause for this; PostgreSQL 18 doesn't.) Support has only one person, and still appears - top-N means "at most N".

Second highest, latest per group, and deduplication

The Nth highest value. "Second highest salary" has a trap: ORDER BY salary DESC OFFSET 1 LIMIT 1 returns the second row, which is the top salary again when two people share it, and returns no row at all when there's no second salary. The robust answers are DENSE_RANK() = 2, or an aggregate that returns one row even when empty:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The latest row per group. Postgres's DISTINCT ON (expr) keeps the first row of each group in the ORDER BY order - compact, but Postgres-only, and the ORDER BY must start with the DISTINCT ON expressions:

SELECT DISTINCT ON (department) department, name, hired_on
FROM employees
ORDER BY department, hired_on DESC;

Deduplication. ROW_NUMBER() partitioned by the key that should be unique and ordered by which copy to keep marks every extra copy with rn > 1. Normalize the key (emails are case-insensitive) and break ties so the choice is deterministic:

SELECT id, email
FROM (SELECT id, email,
             ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created_at DESC, id DESC) AS rn
      FROM signups) AS s
WHERE rn = 1
ORDER BY id;

To actually remove the copies: DELETE FROM signups WHERE id IN (SELECT id FROM (...) WHERE rn > 1) - then add a unique index on lower(email) so they can't come back.

NULLs sort first when descending

PostgreSQL treats NULL as larger than any value: ascending, NULLs come last; descending, they come first. A ranking by bonus puts the person with no bonus at number 1:

SELECT name, bonus, RANK() OVER (ORDER BY bonus DESC) AS rank
FROM bonuses
ORDER BY rank, name;

Write ORDER BY bonus DESC NULLS LAST whenever the column can be NULL (other engines default differently, so say it explicitly either way).

Where it breaks

• A window function in WHERE or HAVING - an error; filter in an outer query.
• ROW_NUMBER without a tiebreaker - which tied row wins can change between runs, and with it the result.
• Picking the wrong ranking function - ROW_NUMBER drops tied rows, RANK leaves gaps that exclude the next value, DENSE_RANK can return more than N rows.
• OFFSET for the Nth value - wrong with ties, and returns no row instead of NULL when there isn't one.
• DESC rankings over a nullable column - NULLs rank first in Postgres. Add NULLS LAST.
• Deduplicating on the raw key - Ava@Example.com and ava@example.com look different to PARTITION BY email.

Tradeoffs

• Window + filter vs DISTINCT ON: DISTINCT ON is shorter and fast for "one row per group", but Postgres-only and limited to one row; ranking functions handle top-N and ties and port to every engine.
• Ranking the whole table vs LATERAL ... LIMIT: for "latest 3 orders for each of 20 customers" in a table of millions, JOIN LATERAL (SELECT ... WHERE customer_id = c.id ORDER BY ordered_at DESC LIMIT 3) can read just those rows through an index, where a window ranks every order first.
• MAX(...) WHERE < MAX(...) vs DENSE_RANK for the second highest: the aggregate version is short and returns NULL when there's none; DENSE_RANK generalizes to any N.

Going further. The deep dive (next tab) shows how the database evaluates a window - one sort per distinct window, a "run condition" that stops early - when an index removes the sort, why LATERAL can still beat it, and how ranking works when the data is spread over many machines.

Check yourself

1. Salaries are 100, 90, 90 and 80. What do ROW_NUMBER, RANK and DENSE_RANK (ordered by salary descending) give the 80?

2. Why does SELECT ... WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3 fail, and what's the fix?

3. What does SELECT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1 return when the two best-paid people earn the same, and when everyone earns the same?

Key takeaways

• A window function computes over related rows (OVER (PARTITION BY ... ORDER BY ...)) and keeps every row.
• ROW_NUMBER never ties; RANK ties and skips; DENSE_RANK ties without gaps. Choose by the requirement's tie rule.
• Window functions run after WHERE/HAVING: filter on them in an outer query.
• The Nth highest value: DENSE_RANK or MAX ... WHERE <, not OFFSET.
• Deduplicate with ROW_NUMBER() OVER (PARTITION BY normalized_key ORDER BY keep_first) = 1; DISTINCT ON is the Postgres shortcut.
• Descending order puts NULLs first in Postgres - add NULLS LAST.