MAX_BY()¶

MAX_BY(value, order_by, tie_breaker) returns the value associated with the maximum order_by value in each group, so you can select the newest, largest, or highest-ranked row alongside an unrelated payload column.

Description¶

MAX_BY(value, order_by, tie_breaker) is an aggregate function that finds the row with the maximum order_by value in each group and returns that row’s value. The optional tie_breaker argument resolves ties deterministically: when multiple rows share the same maximum order_by, the row with the greatest tie_breaker is selected.

The function is commonly used to fetch “the value of the latest record” without a self-join or window function.

Syntax¶

MAX_BY(value, order_by [, tie_breaker])

Arguments¶

Argument	Description
`value`	The column or expression to return for the selected row.
`order_by`	The column or expression that determines which row is selected; the maximum value wins.
`tie_breaker`	Optional. A third expression used to break ties among rows with equal `order_by` values.

Return Value¶

Returns the value of the row with the greatest order_by. If the selected row’s value is NULL, the result is NULL.

Examples¶

DROP DATABASE IF EXISTS max_by_demo;
CREATE DATABASE max_by_demo;
USE max_by_demo;

CREATE TABLE readings (grp INT, value VARCHAR(20), ts BIGINT, tie VARCHAR(20));
INSERT INTO readings VALUES
    (1, 'older', 10, 'a'),
    (1, 'latest-a', 12, 'a'),
    (1, 'latest-z', 12, 'z'),
    (2, 'kept', 1, 'a'),
    (3, 'alpha', 5, 'same'),
    (3, 'beta', 5, 'same');
SELECT grp, MAX_BY(value, ts, tie) AS latest_value
FROM readings GROUP BY grp ORDER BY grp;

DROP DATABASE max_by_demo;