MAX() vs GREATEST() in MySQL: What’s the Difference?

At first glance, the MySQL MAX() and GREATEST() functions perform a similar operation. They both return the maximum value from a range of values. However, there’s a difference between these two functions.

The best way to see the difference between these two functions is to compare their syntax.

Syntax

The syntax for each function goes like this:

MAX([DISTINCT] expr) [over_clause]
GREATEST(value1,value2,...)

So already, they look completely different. The MAX() function accepts the DISTINCT keyword as well as an OVER clause (and the GREATEST() function doesn’t).

However, the key difference between these two functions is in the accepted argument/s. Specifically:

MAX() accepts one argument
GREATEST() accepts multiple arguments

So MAX() is typically used to return the maximum value in a column in a database. The table could contain many rows, but this function returns the one with the maximum value.

GREATEST() on the other hand, returns the maximum-valued argument from the list of arguments passed to it. So you could pass say, 3 arguments to this function and it will return the one with the largest value.

Example 1 – The MAX() Function

Here’s an example to demonstrate the MAX() function.

SELECT MAX(Population) AS 'Result'
FROM City;

Result:

+----------+
| Result   |
+----------+
| 10500000 |
+----------+

This example finds the city with the largest population from the City table. The column that contains the population for each city is called Population.

The key point about this example is that only one argument was supplied to the function, but multiple rows were queried.

If you try to pass multiple arguments to the MAX() function you’ll get an error.

Example 2 – The GREATEST() Function

Here’s an example to demonstrate the GREATEST() function.

SELECT GREATEST(1, 5, 9) AS 'Result';

Result:

+--------+
| Result |
+--------+
|      9 |
+--------+

So in this case, we provide three arguments. Each argument is compared against the other. This is in contrast to the single argument provided to the MAX() function.

If you try to pass a single argument to the GREATEST() function you’ll get an error.