Top-N per group and de-duplication

A very common question

"The highest-paid person in each department", "the latest order of each customer", "the top 3 products in each category": these top-N-per-group questions come up at work and in almost every SQL interview. ORDER BY with LIMIT gives the top N of the whole table, not of each group. Window functions solve it cleanly.

Why not WHERE rn = 1?

The natural first try is to put ROW_NUMBER in WHERE. It fails, because window functions are computed after WHERE has already run (FROM, WHERE, GROUP BY, HAVING, then windows). SQLite says "misuse of window function ROW_NUMBER()"; PostgreSQL says window functions are not allowed in WHERE. An alias does not help either.

SELECT name, salary
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) = 1;
-- Error: misuse of window function ROW_NUMBER()

The pattern: number in a CTE, filter outside

Compute the number in a CTE (a named subquery, from module 6), then filter it in the outer query, where the number is just an ordinary column.

WITH ranked AS (
  SELECT d.name AS dept, e.name, e.salary,
         ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC, e.name) AS rn
  FROM employees e
  JOIN departments d ON d.id = e.dept_id
)
SELECT dept, name, salary
FROM ranked
WHERE rn = 1
ORDER BY dept;
-- Engineering | Meera Iyer     | 250000
-- Finance     | Farhan Ali     | 105000
-- HR          | Lakshmi Pillai | 80000
-- Sales       | Pooja Gupta    | 110000

ROW_NUMBER or RANK?

Think about ties. ROW_NUMBER returns exactly N rows per group, picking among tied rows by the tie-breaker. RANK or DENSE_RANK keeps every tied row. In Engineering, the 3rd highest salary is shared by Anita and Vikram: with ROW_NUMBER and rn <= 3 you get one of them; with RANK and rnk <= 3 you get both. Ask which one the business wants.

QUALIFY in some engines

Snowflake, BigQuery and DuckDB (and some others) have a QUALIFY clause, which filters on a window function directly, like a WHERE for windows: ... QUALIFY ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) = 1. SQLite, PostgreSQL, MySQL and SQL Server do not support QUALIFY, so the CTE pattern is the one that works everywhere.

Latest record per key

The same pattern with ORDER BY ... DESC gives the latest row for each key: the latest order per customer, the current address of each user, the last known price of each product. This is far more reliable than joining on MAX(order_date), which returns several rows if two orders share the latest date.

WITH ranked AS (
  SELECT customer_id, id, order_date, status,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) AS rn
  FROM orders
)
SELECT customer_id, id, order_date, status FROM ranked WHERE rn = 1 ORDER BY customer_id;
-- 1 | 108 | 2025-04-02 | Placed
-- 2 | 106 | 2025-03-15 | Delivered
-- 3 | 104 | 2025-02-20 | Cancelled
-- 4 | 105 | 2025-03-02 | Shipped
-- 5 | 107 | 2025-03-28 | Placed

Finding and removing duplicates

Imported data often has duplicates: the same email signed up three times. Number the rows inside each email by id; row 1 is the one to keep, every row with rn > 1 is a duplicate.

WITH numbered AS (
  SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM signups
)
SELECT id, email FROM numbered WHERE rn > 1 ORDER BY id;
-- 3 | asha@example.com
-- 5 | ravi@example.com
-- 6 | asha@example.com

To actually delete them (module 8 covers DELETE in detail), feed those ids to a DELETE. Always run the SELECT first to check what will go.

DELETE FROM signups
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM signups
  ) WHERE rn > 1
);
-- signups now has ids 1, 2, 4, 7

Common mistakes

• Putting the window function in WHERE. Compute it in a CTE or subquery, then filter outside.
• Using ROW_NUMBER when tied rows should all be kept. Use RANK or DENSE_RANK.
• Forgetting PARTITION BY, so you get the top N of the whole table instead of each group.
• Sorting the wrong way: the latest record needs ORDER BY date DESC inside OVER.
• Deleting duplicates without a preview, or without a stable rule for which copy to keep. Decide (lowest id, newest date) and run the SELECT first.