Categories:

Conditional Expression Functions

GREATEST

Returns the largest value from a list of expressions. If any of the argument values is NULL, the result is NULL. GREATEST supports all data types, including VARIANT.

Syntax

GREATEST( <expr1> [ , <expr2> ... ] )

Arguments

exprN

Expressions can be of any data type, but all expressions within a call should be either the same data type or compatible types.

Usage Notes

  • The first argument determines the return type. If the first type is numeric, then the return type will be ‘widened’ according to the numeric types in the list of all arguments.

    If the first type is not numeric, then all other arguments must be convertible to the first type.

Collation Details

  • The collation specifications of all input arguments must be compatible.

  • The comparisons follow the collation based on the input arguments’ collations and precedences.

  • The collation of the result of the function is the highest-precedence collation of the inputs.

Examples

Basic example:

CREATE TABLE test_table_1_greatest (col_1 INTEGER, col_2 INTEGER, 
    col_3 INTEGER, col_4 FLOAT);
INSERT INTO test_table_1_greatest (col_1, col_2, col_3, col_4) VALUES
    (1, 2,    3,  4.00),
    (2, 4,   -1, -2.00),
    (3, 6, NULL, 13.45);
SELECT
       col_1,
       col_2,
       col_3,
       GREATEST(col_1, col_2, col_3) AS greatest
   FROM test_table_1_greatest
   ORDER BY col_1;
+-------+-------+-------+----------+
| COL_1 | COL_2 | COL_3 | GREATEST |
|-------+-------+-------+----------|
|     1 |     2 |     3 |        3 |
|     2 |     4 |    -1 |        4 |
|     3 |     6 |  NULL |     NULL |
+-------+-------+-------+----------+
SELECT
       col_1,
       col_4,
       GREATEST(col_1, col_4) AS greatest
   FROM test_table_1_greatest
   ORDER BY col_1;
+-------+-------+----------+
| COL_1 | COL_4 | GREATEST |
|-------+-------+----------|
|     1 |  4    |     4    |
|     2 | -2    |     2    |
|     3 | 13.45 |    13.45 |
+-------+-------+----------+