In SQL Server, the GREATEST() function returns the maximum value from a list of one or more expressions.

The GREATEST() function was introduced in SQL Server 2022 (along with the LEAST() function).

Syntax

The syntax goes like this:

GREATEST ( expression1 [ , ...expressionN ] )

So it requires at least one argument, and it can accept multiple arguments. But it supports no more than 254 arguments.

Example

Here’s a simple example to demonstrate:

SELECT GREATEST( 50, 70 );

Result:

70

Here I passed two arguments, and GREATEST() returned the maximum value of the two.

Here’s an example with more arguments:

SELECT GREATEST( 12, 75, 1, 20 );

Result:

75

Once again, the GREATEST() function returned the maximum value.

Character Data

The GREATEST() function accepts various data types, including character data. Here’s an example that compares various strings:

SELECT 
  GREATEST( 'Cat', 'Dog' ) AS r1,
  GREATEST( 'Cat', 'Dog', '!' ) AS r2,
  GREATEST( 'Cat', 'Cattery' ) AS r3;

Result:

r1  r2  r3     
--- --- -------
Dog Dog Cattery

Comparison of character arguments follows the rules of Collation Precedence.

Mixed Data Types

We can pass different data types to the GREATEST() function, as long as they’re comparable and can be implicitly converted to the data type of the argument with the highest precedence. Implicit conversion of all arguments to the highest precedence data type takes place before comparison.

For example:

SELECT GREATEST( 7.85, 5 );

Result:

7.85

Here’s the same example, but using variables to explicitly specify the data type of each argument:

DECLARE @a DECIMAL(3, 2) = 7.85;
DECLARE @b INT = 5;
SELECT GREATEST( @a, @b );

Result:

7.85

But if implicit type conversion between the arguments isn’t supported, the function will fail and return an error:

SELECT GREATEST( 'Five', 10 );

Result:

Msg 245, Level 16, State 1, Server 428cd9af317f, Line 1
Conversion failed when converting the varchar value 'Five' to data type int.

Error 245 was returned due to the incompatible data types. SQL Server needs all arguments to be compatible before it compares them. It does this by performing an implicit data type conversion to the argument with the highest precedence if needed. However, this will only work if the arguments can be converted to that data type. If not, then we get the above error.

Passing NULL Arguments

If all arguments are NULL, then the GREATEST() function returns NULL. But if any of the arguments are not NULL, then any NULL arguments are ignored during comparison.

Example:

SELECT 
  GREATEST( 'Bird', 'Fish', NULL ) AS r1,
  GREATEST( NULL, NULL, NULL ) AS r2;

Result:

r1   r2         
---- -----------
Fish        NULL

Unsupported Types

Not all data types can be used with the GREATEST() function.

Specifically, the following types are not supported; cursor, geometry, geography, image, non-byte-ordered user-defined types, ntext, table, text, and xml.

Also, GREATEST() doesn’t support varchar(max), varbinary(max) or nvarchar(max) exceeding 8,000 bytes. However, these types are supported for arguments that are 8,000 bytes or less, and will be implicitly converted to varchar(n), varbinary(n), and nvarchar(n), respectively, prior to comparison.