Least/Greatest value in an array

Greatest/Least value in an array

Description

GREATEST and LEAST are helpful way to find the greatest value in a row but to do that for an array is trickier.
This snippet lets you return the max/min value from an array while maintaining your data shape.
NOTE: This will exclude NULLS.

SELECT 
   <COLUMN1>...<COLUMNN>,
   MIN(value)
FROM
   <TABLE>
CROSS JOIN UNNEST(<ARRAY_COLUMN>) as value,
group by <COLUMN1>...<COLUMNN>

where:

  • <COLUMN1>...<COLUMNN> is a list of all the other rows you wish to select,

  • <ARRAY_COLUMN> is the column that contains the array from which you want to select the greatest/least value

Example: