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>) [FILTER ([WHERE] <condition>)]
Parameters
| Parameter | Description | Supported input types |
|---|
<result> | The column from which the value is returned | Any type |
<value> | The column that is maximized | Any type |
<condition> | An optional boolean expression to filter rows used in aggregation | BOOL |
Return Types
Same as input type of <result>
Examples
The tournaments table contains the following data:
SELECT name, totalprizedollars FROM tournaments ORDER BY totalprizedollars DESC LIMIT 5;
| name text null | totalprizedollars int null |
|---|
| The Drifting Thunderdome | 24768 |
| The Talladega Thrill | 24747 |
| The Elite Speed Demons Cup | 24346 |
| The Volcanic Venture Rally | 24323 |
| The Drifting Wasteland | 24271 |
Rows: 5Execution time: 6.00ms
tournaments table:
SELECT MAX_BY(name, totalprizedollars) AS maxprizetournament FROM tournaments;
| maxprizetournament text null |
|---|
| The Drifting Thunderdome |
Rows: 1Execution time: 6.87ms
NULL value of the first argument is chosen. The result of the following query is non-deterministic — either 'd' or 'e' may be returned, as both maximize value while having a non-NULL key:
SELECT MAX_BY(key, value)
FROM UNNEST(
ARRAY['a', NULL, 'c', 'd', 'e', NULL],
ARRAY[10, 100, 1, 100, 100, NULL]
) t(key, value);
Rows: 1Execution time: 6.04ms
NULL in the first argument, NULL is returned:
SELECT MAX_BY(key, value) FROM UNNEST(
ARRAY['a', NULL, 'c', 'd', NULL, 'f'],
ARRAY[10, 100, 1, 2, 100, NULL]
) t(key, value);
Rows: 1Execution time: 6.51ms
