MIN_BY() OVER ...

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

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

Syntax

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

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

Notes

• Returns value_expression for the row with minimum key_expression
• Useful for finding associated values with minimum 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 MIN_BY(label, k) OVER (ORDER BY id ASC)).UNWRAP() -- Label at minimum key seen so far
;