ROW_NUMBER vs RANK vs DENSE_RANK?
Practice this questionWANT TO UNDERSTAND THE CONCEPT FIRST?Learn ROW_NUMBER vs RANK vs DENSE_RANK?
Definition:
These window functions assign positions within an ordered partition. ROW_NUMBER gives each row a different number, RANK leaves gaps after ties, and DENSE_RANK does not leave gaps. The desired meaning of “top N” determines which one is correct.
Key points:
-
ROW_NUMBER:
1, 2, 3even when values tie. -
RANK: ties can produce
1, 1, 3. -
DENSE_RANK: ties can produce
1, 1, 2. -
Use a deterministic tie-breaker with ROW_NUMBER if exactly which tied row wins matters.
Comparison — ordered salaries 100, 100, 90:
| Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 100 | 1 | 1 | 1 |
| 100 | 2 | 1 | 1 |
| 90 | 3 | 3 | 2 |
- Without another ordering key, which tied employee gets ROW_NUMBER 1 is not guaranteed.
Interview point:
- “Top three rows” and “top three distinct salaries including ties” are different questions.
Adapted from 05_SQL_POSTGRESQL.md · “ROW_NUMBER vs RANK vs DENSE_RANK?”. Code examples are study fragments. Difficulty and core labels are editorial estimates; version-specific behavior should be checked against your project.