MAX_BY() GROUP BY ...
All functions > GROUP BY > MAX_BY() GROUP BY ...
Returns the maximum value in the group by the given expression.
Syntax
Diagram(
Sequence(
Terminal("MAX_BY"),
Terminal("("),
NonTerminal("expr"),
Terminal(")"),
Choice(0, Skip(),
Sequence(
Terminal("FILTER"),
Terminal("("),
Terminal("WHERE"),
NonTerminal("condition"),
Terminal(")")
)
),
Choice(0, Skip(),
Sequence(
Terminal("GROUP BY"),
OneOrMore(NonTerminal("feature"), Terminal(","))
)
)
)
)| Parameter | Type | Required | Description |
|---|---|---|---|
expr | V | Yes | The value expression to return |
expr_min_max | K | Yes | The expression to maximize |
condition | BOOLEAN | No | The condition to filter the values before aggregation |
feature | FEATURE | No | The features to group by (many features are supported) |
| Parameter | Type | Required | Description |
|---|---|---|---|
expr | V | Yes | The value expression to return |
expr_min_max | K | Yes | The expression to maximize |
num_values | BIGINT | Yes | Number N of values to return |
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