MAX_BY

​

Syntax

​

Aggregate

max_by(value, key)

​

Window

max_by(value, key) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

​

Arguments

​

Aggregate and window

​

Returns

• Type: same SQL type as value
• NULL behavior: Rows with NULL keys are ignored. A selected value can be NULL. All-NULL keys yield NULL.
• Empty input: Returns NULL for an empty global aggregate or frame.

​

Properties

​

Usage notes

• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Supports DISTINCT: Adding DISTINCT does not remove duplicate input values.

​

Examples

​

Select by key

WITH t("value", "key") AS (VALUES ('a', 1), ('b', 2)) SELECT max_by("value", "key") AS result FROM t;

​

NULL selected value

WITH t("value", "key") AS (VALUES ('a', 1), (CAST(NULL AS VARCHAR), 2)) SELECT max_by("value", "key") AS result FROM t;

​

All-NULL ordering keys

WITH t("value", "key") AS (VALUES ('a', CAST(NULL AS INT)), ('b', CAST(NULL AS INT))) SELECT max_by("value", "key") AS result FROM t;

​

Limitations

• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.
• MAX_BY requires exactly two arguments. A three-argument form is not supported.
• In grouped calls, DISTINCT does not remove duplicate input values. Selection can still vary when rows are tied or their order changes.