MIN_BY

Reference material for MIN_BY

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

Syntax

MIN_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 minimized	Any type
<condition>	An optional boolean expression to filter rows used in aggregation	BOOL

Return Types

Same as input type of <result>

Example

The tournaments table contains the following data:

SELECT name, totalprizedollars FROM tournaments ORDER BY totalprizedollars LIMIT 5;

name text null	totalprizedollars int null
The French Grand Prix	237
The European Grand Prix	465
The Desert Dash Rally	643
The Turbocharged Championship Series	704
The African Grand Prix	895

Rows: 5Execution time: 6.00ms

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

SELECT MIN_BY(name, totalprizedollars) AS minprizetournament FROM tournaments;

minprizetournament text null
The French Grand Prix

Rows: 1Execution time: 6.62ms

When multiple rows minimize 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 minimize value while having a non-NULL key:

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

min_by text null
d

Rows: 1Execution time: 6.04ms

If all rows minimizing the second argument have NULL in the first argument, NULL is returned:

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

min_by text null
NULL

Rows: 1Execution time: 6.05ms