sharefun2023

Posted on Sep 29

ROW_NUMBER vs RANK vs DENSE_RANK: one of them silently duplicates your Top-N

#postgres #sql #mysql #database

I was reviewing a "top 3 products per category" report last week and found a bug that had been live for months. One category showed four products. Another showed the same product twice.

The query used RANK(). It should have used ROW_NUMBER().

They look interchangeable in a tutorial and behave completely differently the moment your data has ties.

The setup

Here is a small sales table with an obvious tie — two products in the same category sold 120 units:

CREATE TABLE product_sales (
  category   text,
  product    text,
  units_sold int
);

INSERT INTO product_sales VALUES
  ('tools', 'Hammer',   120),
  ('tools', 'Wrench',   120),
  ('tools', 'Saw',       95),
  ('tools', 'Drill',     80);

Two products are tied at 120, one at 95, one at 80. What does "top 3" mean here? That depends entirely on which ranking function you pick.

What each function actually returns

All three are window functions, so they need an OVER (PARTITION BY ... ORDER BY ...) clause. The difference is what they do with equal values:

Function	Ties get	Result on the data above
`ROW_NUMBER()`	arbitrary distinct numbers	1, 2, 3, 4
`RANK()`	the same rank, then it skips	1, 1, 3, 4
`DENSE_RANK()`	the same rank, then it does not skip	1, 1, 2, 3

SELECT
  product,
  units_sold,
  ROW_NUMBER() OVER (ORDER BY units_sold DESC) AS rn,
  RANK()       OVER (ORDER BY units_sold DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY units_sold DESC) AS drnk
FROM product_sales;

product | units_sold | rn | rnk | drnk
--------+------------+----+-----+-----
Hammer  |        120 |  1 |   1 |    1
Wrench  |        120 |  2 |   1 |    1
Saw     |         95 |  3 |   3 |    2
Drill   |         80 |  4 |   4 |    3

Notice RANK() jumps from 1 to 3. That gap is by design — it answers "how many rows are strictly better than this one, plus one". ROW_NUMBER() answers "give me a stable row index". Those are different questions.

How RANK() silently breaks "top N per group"

Now add the category column back and filter to the top 3:

SELECT category, product, units_sold
FROM (
  SELECT
    category, product, units_sold,
    RANK() OVER (PARTITION BY category ORDER BY units_sold DESC) AS rnk
  FROM product_sales
) t
WHERE rnk <= 3;

You get four rows for tools: Hammer, Wrench, Saw, Drill. Both tied products share rank 1, then Saw gets rank 3 and Drill also gets rank 3 — so rnk <= 3 keeps all four.

That is the bug. The report says "top 3" and emits 4 rows. If a downstream system paginates on the count, the totals are wrong. If a human reads it, they wonder why the list is one item longer.

Swap in ROW_NUMBER() and you get exactly 3 rows — but now which of Hammer/Wrench wins is undefined, because the ORDER BY has no tiebreaker.

The fix: ROW_NUMBER with an explicit tiebreaker

Never let the database decide ties for you. Add a deterministic second sort key:

SELECT category, product, units_sold
FROM (
  SELECT
    category, product, units_sold,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY units_sold DESC, product ASC   -- tiebreaker
    ) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

Now the result is stable and reproducible: the same query returns the same three rows every time, in every environment. When someone asks "why is Wrench above Hammer?", the answer is a rule you wrote down, not whatever order the storage engine happened to return.

If ties genuinely deserve equal standing and you want all of them, then RANK() is correct — just be aware you are asking for "at least N".

When DENSE_RANK is what you actually want

DENSE_RANK() is for "which distinct tier is this value in", where you do not want gaps. Ranking price bands, severity levels, or score buckets:

SELECT
  product,
  units_sold,
  DENSE_RANK() OVER (ORDER BY units_sold DESC) AS tier
FROM product_sales;

You get tiers 1, 1, 2, 3 — consecutive, no holes. That reads correctly as "there are three distinct performance levels here". If you used RANK(), the output would say tier 3 for a value that is only the second-best level, which is misleading in a label.

Three things that bite people

1. ROW_NUMBER without a tiebreaker is non-deterministic. The database is free to return either tied row first, and it can change between runs, after an ANALYZE, or after a version upgrade. If the result feeds anything with a count or a diff, you will eventually see a phantom change.

2. Window functions need the filter outside the query. You cannot write WHERE ROW_NUMBER() OVER (...) <= 3 — window functions are evaluated after WHERE. The subquery (or a CTE) is required, not stylistic.

3. Version support. Window functions landed in MySQL 8.0, PostgreSQL 8.4, SQL Server 2005, and SQLite 3.25. On MySQL 5.7 there is no ROW_NUMBER() at all, and the usual workaround is a correlated subquery counting better rows — which is also the clearest way to understand what the rank means:

SELECT p1.category, p1.product, p1.units_sold
FROM product_sales p1
WHERE (
  SELECT COUNT(*) FROM product_sales p2
  WHERE p2.category = p1.category
    AND p2.units_sold > p1.units_sold
) < 3;

That counts how many rows beat this one, which is exactly what RANK() computes — and it has the same tie behaviour, including the extra rows.

The takeaway

Reach for ROW_NUMBER() when you mean "give me N rows". Reach for RANK() when you mean "give me everything at least as good as the Nth". Reach for DENSE_RANK() when the number is a tier label rather than a position.

And whenever you use ROW_NUMBER() for a Top-N, put a tiebreaker in the ORDER BY. It costs nothing and it makes the query reproducible.

If you want to check how a slow or deeply nested query looks after cleanup, the free online SQL formatter runs entirely in your browser — no query text leaves your machine. I wrote up the full window function reference, including running totals and LAG/LEAD comparisons, at SQL window functions explained.

DEV Community