DENSE_RANK() OVER ...

All functions > WINDOW FUNCTION > DENSE_RANK() OVER ...

Returns the rank of the current row within its partition, without gaps. Similar to RANK but consecutive ranks have no gaps.

Syntax

DENSE_RANK() OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

DENSE_RANK() OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

• Assigns a rank to each row within a partition
• Rows with equal ORDER BY values receive the same rank
• No gaps in ranking after ties (e.g., 1, 2, 2, 3)
• Requires ORDER BY clause to determine ranking order
• PARTITION BY creates independent ranking groups
• First row in each partition has rank 1
• Use WF_RANK for ranking with gaps after ties
• Useful when you need consecutive rank values

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1,2,3,4] AS id, ARRAY[100,100,200,200] AS s).TRANSFORM(SELECT DENSE_RANK() OVER (ORDER BY s ASC)).UNWRAP() -- Dense ranks without gaps after ties
;