DENSE_RANK

​

Syntax

​

Window

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

​

Arguments

​

Returns

• Type: BIGINT
• Possible values: Positive integers starting at 1.
• NULL behavior: Returns a non-NULL rank for every input row.
• Empty input: An empty input produces no rows.

​

Properties

​

Usage notes

• Rows with equal ORDER BY values receive the same rank. Ranking restarts within each PARTITION BY group.
• Explicit window-frame clauses are not supported for this function.

​

Examples

​

Tied ordering values

WITH t("value") AS (VALUES (1), (1), (2), (3)) SELECT "value", dense_rank() 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, dense_rank() OVER (PARTITION BY grp ORDER BY v NULLS LAST) AS result
FROM sample ORDER BY grp, row_id;