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 $commit_time BETWEEN run_start_time() AND run_end_time()
    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 $commit_time BETWEEN run_start_time() AND run_end_time()
    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.

In arrays containing null and non-null values, Upsolver ignores the null values when working with the array. For example, greatest_example2 evaluates to 3.0 instead of null.