What is the difference between ROWNUMBER, RANK, and DENSERANK?
SQLLevel: IntermediateWindow Functions
What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?
Hints
- Which rows belong to the partition and frame for each current row?
- Check ordering, ties, frame boundaries and the difference from GROUP BY.
Answer
They all assign a number to each row within a window, ordered by some column. They differ in how they handle ties:
ROW_NUMBER(): assigns a unique sequential number regardless of ties (ties get arbitrary but distinct numbers)RANK(): tied rows get the same rank, then the next rank skips (1, 2, 2, 4)DENSE_RANK(): tied rows get the same rank, but no gaps (1, 2, 2, 3)
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
/*
name | salary | row_num | rnk | dense_rnk
---------|--------|---------|-----|----------
Alice | 100 | 1 | 1 | 1
Bob | 90 | 2 | 2 | 2
Carol | 90 | 3 | 2 | 2
Dave | 80 | 4 | 4 | 3
*/
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT