GREATEST and LEAST

Conditional Function

Description

Returns the largest or smallest value from a list of any number of expressions. (Redshift SQL Language Reference GREATEST and LEAST function)

Grammar Syntax

GREATEST (value [, ...])
LEAST (value [, ...])

This function is fully supported by Snowflake.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT GREATEST(10, 20, NULL, 40, 30) AS greatest, LEAST(10, 20, NULL, 40, 30) AS least, GREATEST(NULL, NULL, NULL) AS nulls;

SELECT LEAST('GERMANY', 'USA') AS LEAST, GREATEST('GERMANY', 'USA') AS GREATEST;

Results

greatest	least	nulls
40	10	25

least	greatest
GERMANY	USA

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
GREATEST_IGNORE_NULLS(10, 20, NULL, 40, 30) AS greatest,
LEAST_IGNORE_NULLS(10, 20, NULL, 40, 30) AS least,
GREATEST_IGNORE_NULLS(NULL, NULL, NULL) AS nulls;

SELECT
LEAST_IGNORE_NULLS(RTRIM('GERMANY'), RTRIM('USA')) AS LEAST,
GREATEST_IGNORE_NULLS(RTRIM('GERMANY'), RTRIM('USA')) AS GREATEST;

Results

greatest	least	nulls
40	10	NULL

least	greatest
GERMANY	USA

Known Issues

There are no know issues

Related EWIs