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 time_filter()
    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 time_filter()
    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 that contain both null and non-null values, SQLake ignores those null values when working with the array; as such, least_example2 evaluates to 0.2 instead of null.