row_number
ranking window function
Applies to: Databricks SQL Databricks Runtime
Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows in 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