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>) [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

The following example finds the tournament name with the highest prize amount in the tournaments table:

SELECT MAX_BY(name, totalprizedollars) AS maxprizetournament FROM tournaments;

maxprizetournament text null
The Drifting Thunderdome

Rows: 1Execution time: 6.87ms

When multiple rows maximize the second argument, an arbitrary non-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);

max_by text null
d

Rows: 1Execution time: 6.04ms

If all rows maximizing the second argument have 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);

max_by text null
NULL

Rows: 1Execution time: 6.51ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Types

Examples

​Syntax

​Parameters

​Return Types

​Examples

Syntax

Parameters

Return Types

Examples