Junior
What is the difference between RANK() and DENSE_RANK()?
sobes.tech AI
Answer from AI
The functions RANK() and DENSE_RANK() are used to assign ranks to rows in a sorted data set, but they differ in how they handle ties (i.e., how they account for gaps in ranks).
-
RANK() assigns the same rank to equal values but skips subsequent ranks. For example, if two elements occupy the 1st position, the next will be ranked 3.
-
DENSE_RANK() also assigns the same rank to equal values but does not skip ranks. In the same example, the next after two firsts with rank 1 will be rank 2.
Example:
| Value | RANK() | DENSE_RANK() |
|---|---|---|
| 100 | 1 | 1 |
| 100 | 1 | 1 |
| 90 | 3 | 2 |
| 80 | 4 | 3 |