Don't miss out on the new features in SQL Server 16.x

The latest version of SQL Server has some exciting updates as well as some very useful new features. Today I would like to look at GREATEST and LEAST, two opposite functions for getting the maximum and minimum values in a list.

GREATEST & LEAST

They respectively return the maximum and minimum values from the list of values. There are some restrictions: the list must contain at least one value; the maximum number of values in the list is 254.
Both functions do not support some data types, for example, cursor, geometry, geography, image, table, ntext, text, xml.

Syntax

GREATEST(value1, value2,…)

LEAST(value1, value2,…)

Input parameters can be, for instance, variables, column names, functions, and any combination of arithmetic, and string operators.

Return data type

The functions return the data type with the highest precedence. If all values in the list are of the same type, it will be returned by the functions. If not, they will try to convert them. As a result, it is important that all values in the list be convertible.

Examples

• Constant numbers: the scale = 4 because it is determined by the scale of 4.7890 (the highest precedence data type).

SELECT GREATEST(4.7890, '5.1', N'3');

Output:
-------
 5.1000

SELECT LEAST(4.7890, '5.1', N'3');

Output:
-------
 3.0000

• Strings

SELECT GREATEST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
John Smith

SELECT LEAST('John Smith', 'Anna Black', N'Bob Low');

Output:
----------
Anna Black

• Combination of variables, table column and constant

CREATE TABLE Currency (
    Currency_Name VARCHAR(3),
    Currency_Rate DECIMAL(10,4)
    );

INSERT INTO Currency
VALUES ('USD', 0.82),
    ('USD', 0.8101),
    ('USD', 0.8058);
GO

DECLARE @Default_Rate DECIMAL(10, 4) = 0.8195;

SELECT Currency_Name,
    Currency_Rate,
    GREATEST(Currency_Rate, 0.8000, @Default_Rate) AS GreatestRate,
    LEAST(Currency_Rate, 0.8000, @Default_Rate) AS LeastRate
FROM Currency;
GO

Output:
Currency_Name Currency_Rate GreatestRate LeastRate   
------------- ------------- ------------ ------------
USD                   .8200        .8200        .8000
USD                   .8101        .8195        .8000
USD                   .8058        .8195        .8000