row_number ranking window function

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

Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition.

Syntax 

row_number()

Arguments

The function takes no arguments.

Returns

An INTEGER.

The OVER clause of the window function must include an ORDER BY clause. Unlike rank and dense_rank, row_number breaks ties.

If the order is not unique, the result is non-deterministic.

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