SQL Server 2022 introduced the LEAST() and GREATEST() functions, which allow us to get the minimum or maximum value from a list of expressions.

The LEAST() function returns the minimum value from a list of one or more expressions.

Syntax

The syntax goes like this:

LEAST ( expression1 [ , ...expressionN ] )

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

Example

Here’s a simple example to demonstrate:

SELECT LEAST( 101, 202 );

Result:

101

Here I passed two arguments, and LEAST() returned the minimum value of the two.

Here’s an example with more arguments:

SELECT LEAST( 999, 865, 101, 202, 7 );

Result:

7

Once again, the LEAST() function returned the minimum value.

Character Data

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

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

Result:

r1  r2  r3     
--- --- -------
Cat !   Cat    

Comparison of character arguments follows the rules of Collation Precedence.

Mixed Data Types

We can pass different data types to the LEAST() 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 LEAST( 3, 2.5 );

Result:

2.5

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

DECLARE @a INT = 3;
DECLARE @b DECIMAL(2, 1) = 2.5;
SELECT LEAST( @a, @b );

Result:

2.5

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

SELECT LEAST( 'Cat', 10 );

Result:

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

We got error 245 due to the incompatible data types. Basically, 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 LEAST() function returns NULL. But if any of the arguments are not NULL, then any NULL arguments are ignored during comparison.

Example:

SELECT 
  LEAST( 'Cat', 'Dog', NULL ) AS r1,
  LEAST( NULL, NULL, NULL ) AS r2;

Result:

r1  r2         
--- -----------
Cat        NULL

Unsupported Types

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

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

Also, LEAST() 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.