GREATEST

Returns the greatest value from the list of arguments.

Syntax

GREATEST(VALUE1 [, VALUE2, ..., VALUEN]) 

Arguments

VALUE

Type: double, bigint, string, timestamp, date, array

A value of any of the above types, provided that all values are of the same type.

Returns

Type: same as input

The largest of the provided values.

When the input values are arrays, the greatest element between all the arrays is returned.

If any of the input values are null, the result is null as well.

Examples

Transformation job example

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
    AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
    SELECT 
        GREATEST(3.4, 66.0, 744.7) AS greatest_example1,
        GREATEST('a', 'z', 'h', 'k') AS greatest_example2,
        GREATEST(date '2022-08-14', date '2022-02-05', null::date) AS greatest_example3
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 1;

Query result

greatest_example1	greatest_example2	greatest_example3
744.7	z	null

Example with arrays

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
    AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
    SELECT 
        GREATEST(ARRAY[1.1, 0.2], ARRAY[3.0]) AS greatest_example1,
        GREATEST(ARRAY[1.1, 0.2], ARRAY[3.0, null::double]) AS greatest_example2,
        GREATEST([1.1, 0.2], [3.0], [null::double]) AS greatest_example3
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE time_filter()
    LIMIT 1;

Query result

greatest_example1	greatest_example2	greatest_example3
3.0	3.0	null

Note that greatest_example3 evaluates to null since the array containing only null is considered a null value.

For arrays that contain both null and non-null values, SQLake ignores those null values when working with the array; as such, greatest_example2 evaluates to 3.0 instead of null.