GREATEST()

This function returns the maximum value from a list of one or more expressions. Currently (in the time when writing this blog post) the function is supported only on Azure SQL (Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics - serverless SQL pool only) but we hope that in the future it will be available also in on-premise versions of SQL.

Syntax:

GREATEST (expression1 [ ,...expressionN ] )

  • expression1, expressionN - A list of comma-separated expressions of any comparable data type. The GREATEST function requires at least one argument and supports no more than 254 arguments. Each expression can be a constant, variable, column name or function, and any combination of arithmetic, bitwise, and string operators. Aggregate functions and scalar subqueries are permitted.

Example

SELECT GREATEST ( 3, 14, 1979 ) AS  "Greatest Value"; 
GO
SELECT GREATEST ('John', N'Doe', 'The example') AS "Greatest String";
GO  
SELECT
	T.ID
	, GREATEST (T.Value1, T.Value2, T.Value3)  AS  "Greatest Value"
	, T.Value1
	, T.Value2
	, T.Value3
FROM 
	dbo.TT T;
GO

Return types:

  • Returns the data type with the highest precedence from the set of types passed to the function.
  • If all arguments have the same data type and the type is supported for comparison, GREATEST will return that type.
  • Otherwise, the function will implicitly convert all arguments to the data type of the highest precedence before comparison and use this type as the return type.
  • For numeric types, the scale of the return type will be the same as the highest precedence argument, or the largest scale if more than one argument is of the highest precedence data type.

Remarks and limitations:

All expressions in the list of arguments must be of a data type that is comparable and that can be implicitly converted to the data type of the argument with the highest precedence.

Example

SELECT GREATEST ( '3', '14', 1979 ) AS  "Greatest Value"; 

Implicit conversion of all arguments to the highest precedence data type takes place before comparison. If implicit type conversion between the arguments is not supported, the function will fail and return an error.

Example

SELECT GREATEST ( '3', '14.', 1979 ) AS  "Greatest Value"; 
GO 
SELECT GREATEST ( 'aa', '14', 1979 ) AS  "Greatest Value"; 
GO 

If one or more arguments are not NULL, then NULL arguments will be ignored during comparison. If all arguments are NULL, then GREATEST will return NULL.

Example

SELECT GREATEST ( 3, 14, NULL) AS  "Greatest Value"; 
GO 
SELECT GREATEST ( NULL, NULL, NULL) AS  "Greatest Value"; 
GO 

Comparison of character arguments follows the rules of Collation Precedence.

The following types are not supported for comparison in GREATEST: varchar(max), varbinary(max) or nvarchar(max) exceeding 8,000 bytes, cursor, geometry, geography, image, non-byte-ordered user-defined types, ntext, table, text, and xml.

Example

DECLARE @LargeString NVARCHAR(MAX) = REPLICATE('A',4096);
SELECT GREATEST ( @LargeString, @LargeString, @LargeString) AS  "Greatest Value"; 
GO
DECLARE @XML XML = (SELECT * FROM sys.tables FOR XML AUTO)
SELECT GREATEST ( @XML, @XML, @XML) AS  "Greatest Value"; 
GO

The varchar(max), varbinary(max), and nvarchar(max) data types are supported for arguments that are 8,000 bytes or below, and will be implicitly converted to varchar(n), varbinary(n), and nvarchar(n), respectively, prior to comparison.

For example, varchar(max) can support up to 8,000 characters if using a single-byte encoding character set, and nvarchar(max) can support up to 4,000 byte-pairs (assuming UTF-16 character encoding).

 *You can download the complete SQL Script with all examples from the post here: SQL Script.sql (2.34 kb)