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

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
• 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
    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 with gaps after ties
;