Links

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.