DENSE_RANK

Returns the rank of the current row within the partition as specified by the order by clause.

If two or more rows tie they have the same ranking.

The difference between RANK and DENSE_RANK is that DENSE_RANK does not skip values after a tie.

Syntax

DENSE_RANK () OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]
)

Return Type

Integer

Examples

CREATE TABLE dr_example (a int, b int);
INSERT INTO dr_example VALUES(1,2),(2,2),(3,3),(4,3);
SELECT a,b, DENSE_RANK() OVER(ORDER BY b) FROM dr_example;
+------+------+-------------------------------+
| a    | b    | DENSE_RANK() OVER(ORDER BY b) |
+------+------+-------------------------------+
|    1 |    2 |                             1 |
|    2 |    2 |                             1 |
|    3 |    3 |                             2 |
|    4 |    3 |                             2 |
+------+------+-------------------------------+

Last modified: February 16, 2023

Was this article helpful?