AI Hints
Homeโ€บSQL Tutorialโ€บWindow Functions
SQL ยท Lesson 64 of 126

RANK and DENSE_RANK

Handling ties.

โฑ 2 min readโ–ถ Video at 1:31:24๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

What if two items are equal? In a race, if two runners tie for fourth place, they both get fourth. Row number can't do that. It always gives different numbers. So SQL has two more numbering functions, built for ties.

RANK and DENSE_RANK
From the video: RANK and DENSE_RANK
  • Two runners tie for 4th
  • ROW_NUMBER always differs
  • RANK and DENSE_RANK

Three ways to number

Let's rank the months of twenty twenty five by orders. September and October both had ninety three orders. Watch what each function does. Run it. Row number gives them four and five. Rank gives both four, then skips to six. Dense rank gives both four, then carries on with five.

query.sqlsql
WITH monthly AS (
    SELECT strftime('%m', order_date) AS month, COUNT(*) AS orders
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY month
)
SELECT month, orders,
       ROW_NUMBER() OVER (ORDER BY orders DESC) AS row_num,
       RANK()       OVER (ORDER BY orders DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY orders DESC) AS dense
FROM monthly
LIMIT 6;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ month โ”‚ orders โ”‚ row_num โ”‚ rnk โ”‚ dense โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 08    โ”‚ 120    โ”‚ 1       โ”‚ 1   โ”‚ 1     โ”‚
โ”‚ 12    โ”‚ 104    โ”‚ 2       โ”‚ 2   โ”‚ 2     โ”‚
โ”‚ 05    โ”‚ 103    โ”‚ 3       โ”‚ 3   โ”‚ 3     โ”‚
โ”‚ 09    โ”‚ 93     โ”‚ 4       โ”‚ 4   โ”‚ 4     โ”‚
โ”‚ 10    โ”‚ 93     โ”‚ 5       โ”‚ 4   โ”‚ 4     โ”‚
โ”‚ 06    โ”‚ 88     โ”‚ 6       โ”‚ 6   โ”‚ 5     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Which one?

So which should you use? Row number when you need exactly one row per position, even with ties. Rank, for sports style results, with gaps. Dense rank, when you want no gaps.

ROW_NUMBER

  • 1, 2, 3, 4, 5
  • No ties, ever
  • Pick exactly one

RANK / DENSE_RANK

  • 1, 2, 3, 4, 4
  • Ties share a number
  • Gaps vs no gaps

Summary

Row number never ties. Rank ties, then leaves a gap. Dense rank ties, without gaps. Next, the most famous window function trick of all.

Key takeaways
  • ROW_NUMBER: no ties
  • RANK: ties, then a gap
  • DENSE_RANK: no gaps
Next up: Top N per Group โ€” the best in each category