ROW_NUMBER vs RANK vs DENSE_RANK?
Understand the concept
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.
Important ideas
-
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.
Can you explain it without the notes?
ROW_NUMBER vs RANK vs DENSE_RANK?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
These window functions assign positions within an ordered partition. ROWNUMBER gives each row a different number, RANK leaves gaps after ties, and DENSERANK does not leave gaps. The desired meaning of “top N” determines which one is correct.
Turn this concept into a clear answer.
- “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?”. Examples are study fragments; framework behavior can depend on your version.