Unit 07.02: ROW_NUMBER, RANK, and what ties do

Unit ID: SQL-M07-U03 - Estimated active time: 15-18 minutes Objective: choose the ranking function whose tie behaviour matches the question.

Three functions, three answers when values tie

• ROW_NUMBER() - always distinct. Ties are broken arbitrarily.
• RANK() - ties share a rank, and the next rank skips.
• DENSE_RANK() - ties share a rank, and the next rank does not skip.

Our data contains a real tie: orders 500 and 501 both total exactly ₹1,000.00.

SELECT order_id,
       order_total,
       ROW_NUMBER()  OVER (ORDER BY order_total DESC) AS rn,
       RANK()        OVER (ORDER BY order_total DESC) AS rk,
       DENSE_RANK()  OVER (ORDER BY order_total DESC) AS dr
FROM orders
QUALIFY order_total = 1000.00
ORDER BY order_id;
-- 500 | 1000.00 | 879 | 878 | 878
-- 501 | 1000.00 | 878 | 878 | 878

Look closely at rn. Order 501 got 878 and order 500 got 879 - but that assignment is arbitrary. Nothing in the query decides which tied row comes first, so a different plan or version could swap them. RANK and DENSE_RANK both give 878 to each, which is deterministic.

Why this matters for "top N"

"The top 878 orders by value" using ROW_NUMBER will include exactly one of orders 500 and 501, chosen unpredictably. Using RANK includes both. Neither is wrong - but you must know which behaviour you asked for, because the two produce different lists.

Making ROW_NUMBER deterministic

Add a unique tie-breaker, exactly as in Module 2:

SELECT order_id,
       ROW_NUMBER() OVER (ORDER BY order_total DESC, order_id ASC) AS rn
FROM orders
QUALIFY order_total = 1000.00
ORDER BY rn;
-- 500 | 878
-- 501 | 879

Now order 500 always precedes 501, on every run and every engine.

Choosing between them

• Assigning unique sequence numbers (pagination, deduplication) → ROW_NUMBER with a tie-breaker.
• Competition-style ranking where ties genuinely share a position → RANK.
• Ranking where you want consecutive positions with no gaps → DENSE_RANK.

Practice

Write a query returning the single highest-value order per status, and explain which function you chose and why.

SELECT status, order_id, order_total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY status ORDER BY order_total DESC, order_id) = 1
ORDER BY status;
-- completed | 608  | 4996.00
-- pending   | 1000 | 1500.00

Takeaway

ROW_NUMBER gives distinct numbers but arbitrary order on ties - always add a unique tie-breaker. RANK and DENSE_RANK share ranks and differ only in whether the next number skips.

---

Mark complete