GREATEST function to return the maximum value - MaxCompute

Returns the maximum value from a list of arguments. By default, NULL is treated as the minimum value and does not affect the result — unlike the SQL standard, which propagates NULL to the output.

Syntax

greatest(<var1>, <var2>[,...])

Returns the same data type as the inputs, or a widened type when inputs are of mixed types.

Parameters

Parameter	Required	Type	Description
`var`	Yes	BIGINT, DOUBLE, DECIMAL, DATETIME, DATE, or STRING	Values to compare. Provide two or more arguments.

Return value

Same type: When all inputs share the same data type, the return value is that type.

Mixed types: When inputs have different data types, MaxCompute applies implicit conversion:

Input types	Return type
DOUBLE, BIGINT, STRING	DOUBLE
DECIMAL, DOUBLE, BIGINT, STRING	DECIMAL
STRING, DATETIME	DATETIME

NULL handling:

Mode	Behavior
Default	NULL is treated as the minimum value. `greatest(expr, NULL)` returns `expr`.
Hive-compatible (`set odps.sql.hive.compatible=true;`)	Any NULL input causes the function to return NULL.

MaxCompute's default NULL behavior differs from the SQL standard. The SQL standard specifies that any NULL input returns NULL. If you are migrating from a standard-compliant database, set odps.sql.hive.compatible=true to match that behavior.

Examples

Example 1: Mixed types — implicit conversion to DECIMAL

-- Returns 9.9.
SELECT greatest(2.5, 5, 3.14BD, '9.9');

The inputs are DOUBLE (2.5), BIGINT (5), DECIMAL (3.14BD), and STRING ('9.9'). MaxCompute promotes all inputs to DECIMAL (the widest type in the group), and 9.9 is the maximum.

Example 2: NULL with DATETIME — NULL treated as minimum

-- Returns 2025-10-01 12:00:00.
SELECT greatest(datetime'2025-10-01 12:00:00', null) AS result;

NULL is treated as the minimum, so the non-NULL DATETIME value is returned.

Example 3: Multiple DATE values

-- Returns 2026-01-01.
SELECT greatest(date'2025-10-01', date'2025-10-03', date'2026-01-01', date'2025-02-02') AS result;

Example 4: STRING compared as DATETIME (data type edition 1.0)

-- Returns 2025-10-02 12:00:00.
set odps.sql.type.system.odps2=false;
SELECT greatest(datetime'2025-10-01 12:00:00', '2025-10-02 12:00:00') AS result;

When odps.sql.type.system.odps2=false (data type edition 1.0), a STRING in yyyy-mm-dd hh:mi:ss format is implicitly converted to DATETIME before the comparison. For details, see Data type edition 1.0.

Related functions

GREATEST is a mathematical function. For other functions for data computation and transformation, see Mathematical functions.