rank ranking window function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

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

Syntax

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 dense_rank, rank will produce gaps in the ranking sequence. Unlike row_number, rank does not break ties.

If the order is not unique, the duplicates share the same relative earlier 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