How to Use the GREATEST() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the GREATEST() function in MySQL.

Using the GREATEST() function in MySQL

The GREATEST() function in MySQL is used to return the greatest value from a list of expressions. It can take two or more arguments and returns the argument with the highest value.

Syntax

Here’s the syntax of the GREATEST() function:

GREATEST(expression1, expression2, expression3, ...)

where expression1, expression2, expression3, and so on are the expressions whose values you want to compare.

Examples

Using GREATEST() with two arguments

SELECT GREATEST(25, 35) AS greatest_value;

Output:

+---------------+
| greatest_value|
+---------------+
|      35       |
+---------------+

In this example, we passed two values 25 and 35 to the GREATEST() function, and it returned the greatest value, which is 35.

Using GREATEST() with three arguments

SELECT GREATEST(15, 25, 35) AS greatest_value;

Output:

+---------------+
| greatest_value|
+---------------+
|      35       |
+---------------+

In this example, we passed three values to the GREATEST() function, and it returned the greatest value, which is 35.

Using GREATEST() with columns of a table

SELECT GREATEST(column1, column2, column3) AS greatest_value FROM table_name;

In this example, we passed the columns of a table to the GREATEST() function, and it returned the greatest value for each row.

Using GREATEST() with NULL values

SELECT GREATEST(NULL, 10, 20) AS greatest_value;

Output:

+---------------+
| greatest_value|
+---------------+
|      20       |
+---------------+

In this example, we passed NULL as the first argument to the GREATEST() function, and it returned the greatest value among the non-NULL values, which is 20.

Using GREATEST() in a WHERE clause

SELECT column1, column2, column3 
FROM table_name
WHERE GREATEST(column1, column2, column3) > 50;

In this example, we used the GREATEST() function in a WHERE clause to filter the rows where the greatest value of the three columns is greater than 50.