SQL Server 2022 introduced the GREATEST() function that returns the maximum value from a list of values. You may be thinking, “but there’s already a MAX() function that returns the maximum value, so why the need for another function that does the same thing?”.

Well here’s the thing – they don’t do the same thing. They’re actually quite different functions, used in different scenarios.

If you’re wondering what the difference is between the MAX() and GREATEST() functions, read on to find out.

Definitions

First, let’s check the official definitions for each of these two functions:

• MAX(): Returns the maximum value in the expression.
• GREATEST(): Returns the maximum value from a list of one or more expressions.

I’ve bolded the key difference between these functions. The MAX() function operates on just a single expression, whereas the GREATEST() function operates on one or more expressions.

Also, the MAX() function can have an OVER clause (allowing it to operate as a window function).

Example

Suppose we have the following table:

SELECT * FROM Scores;

Result:

PlayerId    Game1       Game2       Game3      
----------- ----------- ----------- -----------
          1          20          30          15
          2          40          19          30
          3          10          20          50

The GREATEST() Function

We can use the GREATEST() function against the above table like this:

SELECT 
  PlayerId,
  GREATEST( Game1, Game2, Game3 ) AS "Highest Score"
FROM Scores;

Result:

PlayerId    Highest Score
----------- -------------
          1            30
          2            40
          3            50

Here, I passed three column names to the GREATEST() function. It returned the greatest value from all three columns. It did this for each row in the table.

Doing this allowed me to get each player’s highest score, based on the scores for each game, which are stored in the three columns.

The MAX() Function

Here’s an example that uses the MAX() function against the same table:

SELECT 
  MAX(Game1) AS "Highest Score from Game 1"
FROM Scores;

Result:

Highest Score from Game 1
-------------------------
                       40

Here, I used the MAX() function to return the highest value from one specific column.

This function only accepts one argument, so we can’t pass three arguments like we did with the GREATEST() function. If we try to do that we get an error:

SELECT 
  MAX(Game1, Game2, Game3) AS "Highest Score"
FROM Scores;

Result:

Msg 174, Level 15, State 1, Server 69b0575657b5, Line 14
The MAX function requires 1 argument(s).

However, we can include multiple MAX() calls in our SELECT list:

SELECT 
  MAX(Game1) AS "Game 1",
  MAX(Game2) AS "Game 2",
  MAX(Game3) AS "Game 3"
FROM Scores;

Result:

Game 1      Game 2      Game 3     
----------- ----------- -----------
         40          30          50

And we can use an OVER clause with MAX():

SELECT 
  PlayerId,
  MAX( Game1 ) OVER( ORDER BY PlayerId ) AS "Game 1",
  MAX( Game2 ) OVER( ORDER BY PlayerId ) AS "Game 2",
  MAX( Game3 ) OVER( ORDER BY PlayerId ) AS "Game 3"
FROM Scores;

Result:

PlayerId    Game 1      Game 2      Game 3     
----------- ----------- ----------- -----------
          1          20          30          15
          2          40          30          30
          3          40          30          50

In this case, I used an ORDER BY clause in the OVER clause. The MAX() function was applied to each row, and its result reflected the maximum value until that point. In Game 1 the first row is 20 because the first player has a score of 20.

The second row is 40, because the second player got 40 and that’s the highest score so far.

The third row is also 40, even though that player only got a score of 10. The reason it remains at 40 is because 40 is the maximum value so far. We’re basically getting a “running maximum” as the rows are processed and returned. The value only changes if there’s a new maximum. Otherwise it remains at its current maximum.

The same logic is applied to the other two columns, because we’re applying MAX() with the same OVER clause to those columns.

If we look at Game 3, we can see that the output is no different to the underlying data. That’s because each row has a higher value than the previous one, which results in each row generating a new maximum value.

Another thing we can do with the OVER clause is include a PARTITION BY sub-clause. This allows us to partition our results, thereby having the MAX() function operate within each partition (i.e. effectively resetting at the start of each partition).

So in conclusion, there are some significant differences between the MAX() and GREATEST() functions in SQL Server, and the function we choose will depend on what we need to do.