RANK() OVER ...

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

Returns the rank of the current row within its partition, with gaps. Rows with equal values for the ranking criteria receive the same rank.

Syntax

RANK() [WITHIN (WHERE condition)] OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

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
• Leaves gaps in ranking after ties (e.g., 1, 2, 2, 4)
• 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_DENSE for ranking without gaps
• Useful for top-N queries and percentile calculations

See also

• OVER clause

Examples

SELECT
    -- Ranks with gaps after ties
    f1 := ZIP(ARRAY(1, 2, 3, 4) AS id, ARRAY(100, 100, 200, 200) AS s).TRANSFORM(
        SELECT RANK() OVER (ORDER BY s ASC)
    ).UNWRAP(),
    -- 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 RANK() WITHIN (WHERE keep) OVER (ORDER BY s ASC)).UNWRAP()
;