MAX_BY

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the <value> from the row whose <key> is greatest among all rows in the aggregate (or within each group when used with GROUP BY).

Syntax

max_by(<value>, <key>)

SELECT max_by(<value>, <key>) [IGNORE NULLS | RESPECT NULLS]
FROM <table>
GROUP BY <group_columns>;

Arguments

Required

<value>: Any expression whose result is returned when its row wins the comparison. The aggregate result type matches this expression’s type.
<key>: The expression MAX-BY uses to order the rows. The type must support comparison.

Optional

IGNORE NULLS: Skips rows containing NULL values. This is the default behavior.
RESPECT NULLS: Considers rows containgin NULL values.

Returns

The <value> from the row with the maximum <key> among considered rows.

Null handling:

You can combine max_by with other aggregates, GROUP BY, HAVING, subqueries, and expressions in the arguments (for example, max_by(upper(name), score * 2)).
max_by only supports a single value to sort on. To sort using multiple sort criteria, use FIRST_VALUE WITHIN GROUP.
If the aggregate has no input rows (empty table or empty group), the result is NULL.
If every row has a NULL key, those rows are skipped; if no row has a non-NULL key, the result is NULL.
If the winning row’s <value> is NULL, the result is NULL (the key still determines which row wins).

Tie-breaking: If multiple rows share the same maximum key, one of the tied <value> results is returned; which tied row wins is implementation-dependent.

Considerations

DISTINCT is not supported (for example, max_by(DISTINCT name, score) is a syntax error).
Rows with a NULL <key> are ignored when searching for the maximum key.
You can combine max_by with other aggregates, GROUP BY, HAVING, subqueries, and expressions in the arguments (for example, max_by(upper(name), score * 2)).

Examples

Top scorer by score

Returns the name and score from the row with the highest score.

SELECT max_by(name, score) RESPECT NULLS, max(score) AS top_scorer
FROM (
  VALUES
    (1, 'Alice', 85),
    (2, 'Bob', 92),
    (3, 'Charlie', 78),
SELECT region, max_by(product, revenue), max(revenue) AS top_product
    (5, 'Eve', 88)
) AS t(id, name, score);

Returns 'David'.

Best product per region by revenue

Uses GROUP BY so each region gets its own arg-max product and returns the regions and their maximum revenue.

SELECT region, max_by(product, revenue), max(revenue) AS top_product
FROM (
  VALUES
    ('North', 'A', 100),
    ('North', 'B', 150),
    ('South', 'A', 200),
    ('South', 'B', 120)
) AS sales(region, product, revenue)
GROUP BY region;

Returns one row per region. For example South / 'A' and North / 'B'.

NULL keys and NULL value for the winning row

Keys that are NULL are skipped. In this example, the largest non-NULL key is 15, and that row’s value is NULL.

SELECT max_by(value, key_val) AS result
FROM (
  VALUES
    (10, 5),
    (20, NULL::int),
    (30, 10),
    (NULL::int, 15)
) AS t(value, key_val);

Returns NULL.