ROW_NUMBER

​

Syntax

​

Window

row_number() OVER ([PARTITION BY key] [ORDER BY ordering_key])

​

Arguments

​

Returns

• Type: BIGINT
• Possible values: 1 through the number of rows in the group.
• NULL behavior: Returns a non-NULL number for every input row.
• Empty input: An empty input produces no rows.

​

Properties

​

Usage notes

• Add a unique tie-breaker to ORDER BY when row identity must be stable.
• Explicit window-frame clauses are not supported for this function.

​

Examples

​

Unique ordering

WITH t("value") AS (VALUES (3), (1), (2)) SELECT "value", row_number() OVER (ORDER BY "value") AS result FROM t ORDER BY "value";

​

Restart the calculation in each group

WITH sample(grp, row_id, v) AS (VALUES ('a', 1, 10), ('a', 2, 10), ('a', 3, 20), ('b', 4, 5))
SELECT grp, row_id, v, row_number() OVER (PARTITION BY grp ORDER BY v NULLS LAST, row_id) AS result
FROM sample ORDER BY grp, row_id;