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() [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

DENSE_RANK() [WITHIN (WHERE condition)] 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
    -- Dense ranks without gaps after ties
    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 only matching rows; rows outside WITHIN return NULL
    f2 := ZIP(
        ARRAY(1, 2, 3, 4) AS id,
        ARRAY(20, 10, 20, 40) AS s,
        ARRAY(TRUE, FALSE, TRUE, TRUE) AS keep
    ).TRANSFORM(SELECT DENSE_RANK() WITHIN (WHERE keep) OVER (ORDER BY s ASC)).UNWRAP()
;