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

Three Ranking Functions, One Crucial Difference

PostgreSQL ships three ranking window functions that behave identically until two rows tie on the ORDER BY value. That tie is where bugs are born.

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

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;

Notice Diego: RANK() gives him 4 (positions 2 and 3 were "used up" by the tie), while DENSE_RANK() gives him 3.

Which One Do You Actually Want?

• Leaderboard with Olympic-style medals → RANK(). Two gold medals means no silver.
• "How many distinct price tiers exist below this product?" → DENSE_RANK().
• Strict top-N, exactly N rows, ties broken deterministically → ROW_NUMBER() with a tiebreaker column in the ORDER BY.

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, rather than truncating at exactly three rows.

Keeping It Fast in Production

All three share the same cost: a sort over the partition. As data grows, an unindexed ranking query flips from an in-memory sort to a disk-based one and latency multiplies. PG Monitoring flags exactly these regressions — same SQL, suddenly 5× slower — and points at the missing index on the partition/order columns.