GREATEST

This function returns the greatest value from a list of expr[1...N].

Syntax

GREATEST(expr1 [, expr2, ..., exprN])

Arguments

expr: All expr must have either the same or a compatible data type.

Usage Notes

• If any of the argument values is NULL, NULL is returned.

• When expr are strings, they are compared using nonpadded comparison semantics.

• The string comparison is based on the numeric codes of the characters in the database character set and is performed for entire strings treated as a sequence of bytes, and not character by character.

Examples

select col1, col2, col3, greatest(col1, col2, col3)
        from 
               (           select 7  as col1, 3  as col2, 4   as col3       
                union all  select 9  as col1, 1  as col2, 9   as col3
                union all  select -1 as col1, -6 as col2, -99 as col3
                union all  select 0  as col1, NULL as col2, 100   as col3);

+---------+---------+---------+--------------------------- +
|   COL1  |   COL2  |   COL3  | GREATEST(COL1, COL2, COL3) |
+---------+---------+---------+--------------------------- +
| 7       | 3       | 4       | 7                          |
| 9       | 1       | 9       | 9                          |
| -1      | -6      | -99     | -1                         |
| 0       | NULL    | 100     | NULL                       |
+---------+---------+---------+--------------------------- +

select col1, col2, col3, greatest(col1, col2, col3)
        from 
               (           select '7.77'  as col1, 3      as col2, 4   as col3         
                union all  select '9'     as col1, 1.33   as col2, 9   as col3
                union all  select '-1'    as col1, -6     as col2, -99 as col3
                union all  select '7.77'  as col1, 1.3333 as col2, 100 as col3);

+-------+---------------------+------+----------------------------+
| COL1  |         COL2        | COL3 | GREATEST(COL1, COL2, COL3) |
+-------+---------------------+------+----------------------------+
| 7.77  | 3                   | 4    | 7.77                       |
| 9     | 1.33                | 9    | 9                          |
| -1    | -6                  | -99  | -99                        |
| 7.77  | 1.3333000000000002  | 100  | 7.77                       |
+-------+---------------------+------+----------------------------+

select col1, col2, col3, greatest(col1, col2, col3)
        from 
               (           select 'Alpha'  as col1, 'Betta'  as col2, 'Gamma'      as col3         
                union all  select 'A'      as col1, 'B'      as col2, 'C'          as col3
                union all  select 'A'      as col1, 'B'      as col2, ' C'         as col3
                union all  select 'C'      as col1, 'B'      as col2, 'A'          as col3
                union all  select 'Long'   as col1, 'Longer' as col2, 'Long(very)' as col3);

+-------+--------+------------+----------------------------+
| COL1  | COL2   | COL3       | GREATEST(COL1, COL2, COL3) |
+-------+--------+------------+----------------------------+
| Alpha | Betta  | Gamma      | Gamma                      |
| A     | B      | C          | C                          |
| A     | B      | C          | B                          |
| C     | B      | A          | C                          |
| Long  | Longer | Long(very) | Longer                     |
+-------+--------+------------+----------------------------+