MAX_BY

Returns the value of the first argument for the row that contains the maximum of the second argument. If the maximum of the second argument is not unique, an arbitrary non-NULL value of the first argument is returned from the set of rows that maximize the second argument. If the first argument is NULL for all rows maximizing the second argument, NULL is returned.

Syntax

MAX_BY(<result>, <value>)

Parameters

Return Types

Same as input type of

Example

For this example, see the following table, tournaments:

In the example below, MAX_BY is used to find the name of the tournament with the highest total prize.

SELECT
	MAX_BY(name, totalprizedollars) as maxprizetournament
FROM
	tournaments

Returns: The Drifting Thunderdome

When multiple rows maximize the second argument, an arbitrary one is chosen, preferring non-NULL values of the first argument:

SELECT MAX_BY(key, value)
FROM UNNEST(
    ['a', NULL, 'c', 'd', 'e', NULL],
    [10,  100,   1,  100, 100, NULL]
) t(key, value)

Returns 'd' or 'e', as rows 2, 4, and 5 maximize the second argument, but the first argument is NULL for row 2. Because non-NULL values of the first argument exist for the other rows, one of those values is returned. Which of them is non-deterministic, hence this query may return either 'd' or 'e'.

However, if all rows maximizing the second argument are NULL in the first argument, NULL is returned:

SELECT MAX_BY(key, value)
FROM UNNEST(
    ['a', NULL, 'c', 'd', NULL, 'f'],
    [10,  100,   1,   2,  100, NULL]
) t(key, value)

Returns NULL.