In MariaDB, GREATEST() is a built-in function that returns the greatest (i.e. largest/maximum-valued) argument from its list of arguments.
To return the minimum-valued argument, use LEAST().
Syntax
The syntax goes like this:
GREATEST(value1,value2,...)Where value1,value2,… is two or more arguments for which to return the greatest.
Example
Here’s an example:
SELECT GREATEST(3, 8);Result:
+----------------+ | GREATEST(3, 8) | +----------------+ | 8 | +----------------+
Here’s another example that uses more arguments:
SELECT GREATEST(3, 8, 9, 12, 80, 7, 4);Result:
+---------------------------------+ | GREATEST(3, 8, 9, 12, 80, 7, 4) | +---------------------------------+ | 80 | +---------------------------------+
Expressions
The arguments can include expressions like this:
SELECT GREATEST(2 * 3, 1 * 3);Result:
+------------------------+ | GREATEST(2 * 3, 1 * 3) | +------------------------+ | 6 | +------------------------+
Strings
The arguments don’t need to be numeric. For example, here’s a comparison of strings:
SELECT GREATEST('a', 'b', 'z');Result:
+-------------------------+
| GREATEST('a', 'b', 'z') |
+-------------------------+
| z |
+-------------------------+
Dates
Here’s a comparison of date strings:
SELECT GREATEST('2020-01-01', '2021-01-01');Result:
+--------------------------------------+
| GREATEST('2020-01-01', '2021-01-01') |
+--------------------------------------+
| 2021-01-01 |
+--------------------------------------+
Mixed Types
The MariaDB documentation states that if any argument is a case-sensitive string, the arguments are compared as case-sensitive strings. However, you may find that mixing types can produce unexpected results, and a warning.
Example:
SELECT GREATEST('Cat', 10);Result:
+---------------------+
| GREATEST('Cat', 10) |
+---------------------+
| 10 |
+---------------------+
1 row in set, 1 warning (0.000 sec)
Check the warning:
SHOW WARNINGS;Result:
+---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'Cat' | +---------+------+-----------------------------------------+
Null Arguments
GREATEST() returns null if any argument is null:
SELECT GREATEST(1, null, 3);Result:
+----------------------+ | GREATEST(1, null, 3) | +----------------------+ | NULL | +----------------------+
Missing Arguments
Calling GREATEST() with the wrong number of arguments, or without any arguments results in an error:
SELECT GREATEST();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'GREATEST'
And:
SELECT GREATEST(10);Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'GREATEST'