SQL Window Functions Explained: ROW_NUMBER, RANK and DENSE_RANK

Ask three analysts to pull the top three products per region and you'll get three different queries. One will contain a self-join. One will contain a correlated subquery. The third will be four lines long, because somebody reached for a window function. Ranking functions let every row keep its identity while still being compared against its peers — something GROUP BY simply cannot do.

ROW_NUMBER, RANK and DENSE_RANK all answer a version of the question "where does this row sit?" The differences only show up when two rows tie, which in sales data happens far more often than you would expect: matching revenue, matching order counts, matching timestamps to the second.

The OVER clause is where the work happens

All three functions need an OVER() clause. Whatever you put inside the brackets defines the group each row is judged against.

• PARTITION BY splits the data into groups. PARTITION BY region means each region is ranked separately, starting again at 1.
• ORDER BY sets the order within each group. ORDER BY revenue DESC puts the biggest numbers first, so rank 1 is the best performer.
• A frame clause such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW matters for aggregates like SUM and AVG, but not for these three ranking functions. They always look at the whole partition.

One fact is worth memorising early: window functions are evaluated after WHERE, GROUP BY and HAVING, but before the final ORDER BY and LIMIT. That single detail explains most of the errors people run into.

ROW_NUMBER: a strict queue

ROW_NUMBER() hands out 1, 2, 3, 4 with no gaps and no shared places. If two rows look identical in every column you have selected, one still gets 1 and the other gets 2. The engine decides which, and it is under no obligation to make the same choice the next time you run the query.

That makes ROW_NUMBER the right tool for two jobs: pagination, and keeping exactly one row per entity. If you want the most recent order per customer, or the first price recorded for each product, ROW_NUMBER puts a 1 beside the row you want and you filter on it.

RANK and DENSE_RANK: ties, with and without gaps

Say two Yorkshire products both turned over £4,200 last quarter, and the next one down managed £3,100.

RANK gives both of the £4,200 products a 1, skips 2 entirely, then gives the £3,100 product a 3. It answers the question "how many rows are strictly better than this one?" Two golds, no silver — exactly like a sports medal table.

DENSE_RANK gives both a 1, then hands the £3,100 product a 2. No gaps. It answers "how many distinct values are better than mine?"

A rough rule: use RANK when positions matter and gaps carry meaning — league tables, leaderboards, anything where "who is ahead of whom" is the point. Use DENSE_RANK when you are counting tiers rather than people, such as the top three distinct price bands or the three most common order sizes.

A worked example with UK sales

Imagine a table called quarterly_sales with columns for product, region and revenue. One query shows all three functions side by side:

SELECT product, region, revenue,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk
FROM quarterly_sales;

Feed it three Yorkshire rows — Kettle £4,200, Toaster £4,200, Blender £3,100 — and three London rows — Kettle £9,800, Blender £7,500, Toaster £7,500 — and you get this:

• Yorkshire, Kettle, £4,200 → row_num 1, rnk 1, dense_rnk 1
• Yorkshire, Toaster, £4,200 → row_num 2, rnk 1, dense_rnk 1
• Yorkshire, Blender, £3,100 → row_num 3, rnk 3, dense_rnk 2
• Greater London, Kettle, £9,800 → row_num 1, rnk 1, dense_rnk 1
• Greater London, Blender, £7,500 → row_num 2, rnk 2, dense_rnk 2
• Greater London, Toaster, £7,500 → row_num 3, rnk 2, dense_rnk 2

Two things stand out. In Yorkshire, the tie at the top pushes Blender from second to third under RANK, but only to second under DENSE_RANK. And in London, the £7,500 tie produces rnk 2 twice with no 3 at all, while dense_rnk simply sits at 2. Notice too that Blender and Toaster could easily swap their row_num values on another run — nothing in the ORDER BY separates them.

Pitfalls worth knowing before you ship

1. Window functions cannot go in WHERE. Filter on ROW_NUMBER() = 1 in the same query and it will fail. Put the ranking in a CTE or subquery and filter the outer query, or use QUALIFY if your engine supports it — Snowflake, BigQuery, DuckDB and Databricks SQL all do.
2. Leaving out the inner ORDER BY. Some engines will run it without complaint and give you arbitrary results. Always state the order explicitly.
3. Forgetting PARTITION BY. You end up ranking the whole country instead of each region. This creeps in when a query is copied from one grain to another.
4. Trusting ROW_NUMBER on ties. Add a tiebreaker — ORDER BY revenue DESC, product ASC — so the output is repeatable and your tests stay green.
5. Assuming NULL behaviour. PostgreSQL and Oracle sort NULLs as higher than any value; SQL Server and MySQL treat them as lower. Be explicit with NULLS FIRST or NULLS LAST, or filter them out before ranking.
6. Expecting rows to collapse. These functions add a column, not a summary. You get one row per product, each carrying its rank.

Choosing between them

Three questions will settle it almost every time. Do you need exactly one row per group, with ties broken by a rule you control? Use ROW_NUMBER. Do you need genuine ties preserved and gaps to reflect how many rows sit above? Use RANK. Do you need distinct levels with no gaps, so that "top three" means three tiers rather than three rows? Use DENSE_RANK.

Then do one more thing before the query goes anywhere near a dashboard: build a small test set that contains deliberate ties, including a tie at the top of a partition and a tie in the middle. Run all three functions against it and read the numbers carefully. Ten minutes spent on that will save you a far more awkward conversation about why the monthly sales leaderboard changed overnight.

Photo: Atlantic Ambience / Pexels