SQL Interview Question: ROW_NUMBER vs RANK vs DENSE_RANK

Short answer: all three number rows in a given order. They differ only in how they handle ties.

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM players;

name   score  row_num  rnk  dense
Asha     95       1      1      1
Ravi     90       2      2      2
Meera    90       3      2      2
Kiran    85       4      4      3

• ROW_NUMBER - always unique (1, 2, 3, 4); ties are broken arbitrarily unless you add more ORDER BY columns.
• RANK - ties share a rank and leave a gap (1, 2, 2, 4), like sports standings.
• DENSE_RANK - ties share a rank with no gap (1, 2, 2, 3).

Which to use

• Deduplicating or "latest row per group": ROW_NUMBER, keeping row_num = 1.
• Competition rankings: RANK.
• "Nth highest value" or top N distinct levels: DENSE_RANK.

Per group with PARTITION BY

-- Latest order per customer
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Window functions are evaluated after WHERE, so you filter on them in an outer query (or with QUALIFY in databases that support it).