How the GREATEST() function works in Mariadb?

The GREATEST() function is a comparison function that returns the greatest value among a list of values. It can be used to compare numeric, string, date, or time values. The GREATEST() function is compatible with the SQL standard and supports various data types, such as INT, DECIMAL, VARCHAR, DATE, TIME, and DATETIME.

Syntax

The syntax of the GREATEST() function is as follows:

GREATEST(value1, value2, ..., valueN)

where value1, value2, …, valueN are the values to be compared. The function returns the greatest value among the list of values. If any of the values is NULL, the function returns NULL. If the values have different data types, the function performs implicit type conversion according to the comparison rules.

Examples

Example 1: Compare numeric values

The following example uses the GREATEST() function to compare three numeric values and return the greatest one.

SELECT GREATEST(10, 20, 15) AS greatest;

The function compares the three values and returns the largest one. The output is:

+----------+
| greatest |
+----------+
|       20 |
+----------+

The greatest value among 10, 20, and 15 is 20.

Example 2: Compare string values

The following example uses the GREATEST() function to compare three string values and return the greatest one.

SELECT GREATEST('apple', 'banana', 'cherry') AS greatest;

The function compares the three values and returns the largest one based on the lexicographical order. The output is:

+----------+
| greatest |
+----------+
| cherry   |
+----------+

The greatest value among ‘apple’, ‘banana’, and ‘cherry’ is ‘cherry’.

Example 3: Compare date values

The following example uses the GREATEST() function to compare three date values and return the greatest one.

SELECT GREATEST('2024-01-01', '2024-02-01', '2024-01-31') AS greatest;

The function compares the three values and returns the largest one based on the chronological order. The output is:

+------------+
| greatest   |
+------------+
| 2024-02-01 |
+------------+

The greatest value among ‘2024-01-01’, ‘2024-02-01’, and ‘2024-01-31’ is ‘2024-02-01’.

Example 4: Compare mixed values

The following example uses the GREATEST() function to compare three mixed values and return the greatest one.

SELECT GREATEST(10, '20', '10a') AS greatest;

The function compares the three values and performs implicit type conversion according to the comparison rules. The output is:

+----------+
| greatest |
+----------+
|       20 |
+----------+

The greatest value among 10, ‘20’, and ‘10a’ is 20. The function converts the string ‘20’ to a numeric value and compares it with 10. The function also converts the string ‘10a’ to a numeric value, but since it is not a valid number, it returns 0. Therefore, the greatest value is 20.

Some of the functions that are related to the GREATEST() function are:

LEAST(): This function is the opposite of the GREATEST() function. It returns the smallest value among a list of values. It has the same syntax and behavior as the GREATEST() function. For example, LEAST(10, 20, 15) returns 10.
MAX(): This function returns the maximum value of a set of values or a column. It has a different syntax and behavior from the GREATEST() function. For example, MAX(10, 20, 15) returns an error, while MAX(column_name) returns the maximum value of the column.
COALESCE(): This function returns the first non-null value among a list of values. It has the same syntax as the GREATEST() function, but a different behavior. For example, COALESCE(10, NULL, 20) returns 10, while GREATEST(10, NULL, 20) returns NULL.

Conclusion

The GREATEST() function is a useful function to compare and return the greatest value among a list of values. It supports various data types and follows the SQL standard. It can be used to compare numeric, string, date, or time values. It has some related functions that have similar or different functionalities. The GREATEST() function is a powerful tool for data analysis and manipulation in Mariadb.