rank ranking window function

Applies to: Databricks SQL 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

Related functions

• dense_rank ranking window function
• row_number ranking window function
• cume_dist analytic window function
• Window functions