MAX_BY() GROUP BY ...

All functions > GROUP BY > MAX_BY() GROUP BY ...

Returns the maximum value in the group by the given expression.

Syntax

MAX_BY(expr)
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

MAX_BY(expr)
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

Notes

• Returns the value of expr at the row where expr_min_max is largest
• Useful for finding associated values at extremes (e.g., name of highest scorer)
• NULL values in expr_min_max are excluded
• Returns NULL if all values are NULL or group is empty
• With num_values parameter, returns array of top N associated values
• Can be used with WHERE clause to filter before aggregation
• Can be used with GROUP BY clause for grouped aggregation

Examples

SELECT
    f1 := ZIP(ARRAY['a', 'b', 'c'] AS label, ARRAY[3, 1, 2] AS score).TRANSFORM(SELECT MAX_BY(label, score)).UNWRAP_ONE() -- Label at the maximum score
;