row_number

Every row receives a different number, including rows with equal ordering values. Add a unique tie-breaking column to the window ORDER BY when the assignment must be repeatable.

Syntax

text

row_number() OVER ([PARTITION BY expression, ...] ORDER BY expression, ...)

Arguments The function takes no arguments. PARTITION BY starts an independent ranking for each group; without it, all selected rows form one partition. ORDER BY inside OVER determines the ranking order. A frame clause does not restrict the rows used for ranking.

Returned value An INTEGER greater than or equal to 1. The window order does not determine the final output order; use an outer ORDER BY to sort the result.

Example Ranking employee salaries

row_number assigns different numbers to the tied salaries because player breaks the tie.

sql

CREATE TABLE salaries (player TEXT, salary INTEGER);
INSERT INTO salaries VALUES ('Ada', 100), ('Grace', 100), ('Linus', 80), ('Sam', 60);

sql

SELECT player, salary,
       row_number() OVER (ORDER BY salary DESC, player) AS result
FROM salaries
ORDER BY salary DESC, player
FORMAT csv;

text

"Ada",100,1
"Grace",100,2
"Linus",80,3
"Sam",60,4

See rank, dense_rank and row_number to compare tie handling, and window functions for partition and frame syntax.

row_number ​

row_number