MAX_BY_NON_NULL()¶

MAX_BY_NON_NULL(value, order_by, tie_breaker) returns the value of the row with the maximum order_by in each group, skipping NULL values so the latest non-null observation is returned even when the newest row has no value.

Description¶

MAX_BY_NON_NULL(value, order_by, tie_breaker) behaves like MAX_BY() but ignores NULL values. When the row with the greatest order_by has a NULL value, the function falls back to the non-null value of the row with the next-greatest order_by.

This is useful when tracking the most recent observed value of a column that can be intermittently NULL, such as a sensor reading or a status field that is only populated occasionally.

Syntax¶

MAX_BY_NON_NULL(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 non-null value of the row with the greatest order_by. If all value values in the group are NULL, the result is NULL.

Examples¶

DROP DATABASE IF EXISTS max_by_non_null_demo;
CREATE DATABASE max_by_non_null_demo;
USE max_by_non_null_demo;

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

DROP DATABASE max_by_non_null_demo;