GREATEST and LEAST

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the largest or smallest value from a list of expressions.

1GREATEST(<value>, ...)
2LEAST(<value>, ...)
3GREATEST(PRESERVE NULLS <value>, ...)
4LEAST(PRESERVE NULLS <value>, ...)

• <value>: An expression to evaluate. Provide two or more arguments. All arguments must be convertible to a common data type, which is the type of the result.

• PRESERVE NULLS: Includes NULL values in the comparison instead of ignoring them.

GREATEST returns the largest value from the list. LEAST returns the smallest value from the list. The result uses the common data type of the arguments.

By default, GREATEST and LEAST ignore NULL values in the list; the result is NULL only if all inputs evaluate to NULL. When PRESERVE NULLS is specified, the result is NULL if any input is NULL.

• GREATEST and LEAST compare values across the arguments within a single row. To find the largest or smallest value down a column across rows, use the aggregate functions MAX and MIN.

Return the largest of several numeric values.

1SELECT GREATEST(3, 7, 2) AS result;

Returns 7.

Return the smallest of several numeric values.

1SELECT LEAST(3, 7, 2) AS result;

Returns 2.

Return NULL when any input is NULL.

1SELECT GREATEST(PRESERVE NULLS 3, NULL, 2) AS result;

Returns NULL because a NULL input is present.

• COALESCE
• Conditional Expressions