ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.
RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:
SELECT name, sal, rank() over(order by sal desc) rank_by_sal FROM EMPLOYEE o
| name | Sal | RANK_BY_SAL |
|---|---|---|
| Hash | 100 | 1 |
| Robo | 100 | 1 |
| Anno | 80 | 3 |
| Darl | 80 | 3 |
| Tomiti | 70 | 5 |
| Pete | 70 | 5 |
| Bhuti | 60 | 7 |
| Meme | 60 | 7 |
| Inno | 50 | 9 |
| Privy | 50 | 9 |
DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:
SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal FROM EMPLOYEE o
| name | Sal | DENSE_RANK_BY_SAL |
|---|---|---|
| Hash | 100 | 1 |
| Robo | 100 | 1 |
| Anno | 80 | 2 |
| Darl | 80 | 2 |
| Tomiti | 70 | 3 |
| Pete | 70 | 3 |
| Bhuti | 60 | 4 |
| Meme | 60 | 4 |
| Inno | 50 | 5 |
| Privy | 50 | 5 |
This article is to be extended with more questions