dense_rank ranking window function (Databricks SQL)

Returns the rank of a value compared to all values in the partition.

Syntax

dense_rank()

Arguments

This function takes no arguments.

Returns

An INTEGER.

The OVER clause of the window function must include an ORDER BY clause. Unlike the function rank ranking window function (Databricks SQL), dense_rank will not produce gaps in the ranking sequence. Unlike row_number ranking window function (Databricks SQL), dense_rank does not break ties. If the order is not unique the duplicates share the same relative later position.

Examples

> SELECT a,
         b,
         dense_rank() OVER(PARTITION BY a ORDER BY b),
         rank() OVER(PARTITION BY a ORDER BY b),
         row_number() OVER(PARTITION BY a ORDER BY b)
    FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1   1   1
 A1 1   1   1   2
 A1 2   2   3   3
 A2 3   1   1   1