RANK() vs DENSE_RANK() vs ROW_NUMBER() in PostgreSQL: The Difference That Trips Everyone

Three PostgreSQL functions look interchangeable — ROW_NUMBER(), RANK() and DENSE_RANK() — and produce identical output, right up until two rows tie. Picking the wrong one there is a classic source of subtly wrong leaderboards and reports. This post nails down the difference once and for all.

Three Ranking Functions, One Crucial Difference

All three are window functions: they number rows according to the ORDER BY inside OVER(), optionally restarting per PARTITION BY group. They behave identically until two rows tie on the order value — and that tie is where the behaviours split:

• ROW_NUMBER() — always unique. Ties are broken arbitrarily. Produces 1, 2, 3, 4.
• RANK() — tied rows share a rank, then the next rank skips the consumed positions. Produces 1, 2, 2, 4.
• DENSE_RANK() — tied rows share a rank, with no gap afterwards. Produces 1, 2, 2, 3.

Syntax

RANK() OVER (
  [PARTITION BY coluna_ou_expr]
  ORDER BY coluna_ou_expr [ASC | DESC]
)

The ORDER BY defines the ranking rule; PARTITION BY, when present, restarts the ranking for each group. DENSE_RANK() and ROW_NUMBER() share the exact same shape — only the tie behaviour differs.

See It Side by Side

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;

Watch Diego, the row right after the tie. Bruno and Carla both scored 90 and share rank 2. RANK() then jumps Diego to 4 — positions 2 and 3 were "used up" by the tie. DENSE_RANK() keeps counting densely and gives Diego 3. ROW_NUMBER() ignored the tie entirely and numbered straight through.

Which One Do You Actually Want?

• Leaderboard with Olympic-style medals → RANK(). Two golds means no silver — exactly the skip behaviour.
• "How many distinct price tiers are below this product?" → DENSE_RANK(), because you care about distinct levels, not positions.
• Strict top-N, exactly N rows, ties broken deterministically → ROW_NUMBER() with a tiebreaker column in the ORDER BY (e.g. ORDER BY score DESC, id).

Top-3 Per Category

SELECT *
FROM (
  SELECT
    product_id, category, revenue,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS tier
  FROM product_sales
) t
WHERE tier <= 3;

Using DENSE_RANK() here means a three-way tie for first still surfaces the next genuinely-different revenue tiers, instead of truncating at exactly three rows the way ROW_NUMBER() would. Choose deliberately: do you want "the top 3 products" (ROW_NUMBER()) or "products in the top 3 revenue tiers" (DENSE_RANK())? They differ precisely when ties exist.

The Other Ranking Functions

The same family includes three more worth knowing:

• NTILE(n) — splits the ordered rows into n roughly equal buckets. NTILE(4) gives you quartiles, NTILE(100) percentiles.
• PERCENT_RANK() — relative rank from 0 to 1: "this row is better than 80% of the rest."
• CUME_DIST() — cumulative distribution, the fraction of rows at or below the current one.

Keeping It Fast in Production

All of these share one cost: a sort over the partition. As data grows, an unindexed ranking query flips from an in-memory sort to a disk-based external merge and latency multiplies — silently. A composite index matching (partition_col, order_col) lets the window read pre-sorted and skip the sort. PG Monitoring flags exactly these regressions — same SQL, suddenly 5× slower — and points at the missing index on the partition/order columns.