MAX_BY() OVER ...

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

Returns the value of the first expression corresponding to the maximum value of the second expression in the window frame.

Syntax

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

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

Notes

• Returns value_expression for the row with maximum key_expression
• Useful for finding associated values with maximum keys
• Returns same type as first expression
• NULL values in key are ignored

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1,2,3] AS id, ARRAY['c','a','b'] AS label, ARRAY[3,1,2] AS k).TRANSFORM(SELECT MAX_BY(label, k) OVER (ORDER BY id ASC)).UNWRAP() -- Label at maximum key seen so far
;