LEAST

This function returns the smallest of the provided values.

Syntax

LEAST(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 smallest of the provided values.

When the input values are arrays, the smallest 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 
        LEAST(3.4, 66.0, 744.7) AS least_example1,
        LEAST('a', 'z', 'h', 'k') AS least_example2,
        LEAST(date '2022-08-14', date '2022-02-05', null::date) AS least_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

least_example1	least_example2	least_example3
3.4	a	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 
        LEAST(ARRAY[1.1, 0.2], ARRAY[3.0]) AS least_example1,
        LEAST(ARRAY[1.1, 0.2], ARRAY[3.0, null::double]) AS least_example2,
        LEAST([1.1, 0.2], [3.0], [null::double]) AS least_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

least_example1	least_example2	least_example3
0.2	0.2	null

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

For arrays containing null and non-null values, Upsolver ignores those null values when working with the array. For example, least_example2 evaluates to 0.2 instead of null.