Introducing the GREATEST and LEAST T-SQL functions in Azure Synapse Analytics

We are excited to announce that the GREATEST and LEAST T-SQL functions are now generally available in Azure Synapse Analytics (serverless SQL pools only).

This post describes the functionality and common use cases of GREATEST and LEAST in Azure Synapse Analytics, as well as how they provide a more concise and efficient solution for developers compared to existing T-SQL alternatives.

 

Functionality

 

GREATEST and LEAST are scalar-valued functions and return the maximum and minimum value, respectively, of a list of one or more expressions.

 

The syntax is as follows:

GREATEST ( expression1 [ ,...expressionN ] )
LEAST ( expression1 [ ,...expressionN ] )

 

As an example, let’s say we have a table CustomerAccounts and wish to return the maximum account balance for each customer:

 

CustomerID	Checking	Savings	Brokerage
1001	$ 4,294.10	$ 14,109.84	$ 3,000.01
1002	$ 51,495.00	$ 97,103.43	$ 0.02
1003	$ 10,619.73	$ 33,194.01	$ 5,005.74
1004	$ 24,924.33	$ 203,100.52	$ 10,866.87

 

Prior to GREATEST and LEAST, we could achieve this through a searched CASE expression:

SELECT CustomerID, GreatestBalance =
    CASE
        WHEN Checking >= Savings and Checking >= Brokerage THEN Checking
        WHEN Savings > Checking and Savings > Brokerage THEN Savings
        WHEN Brokerage > Checking and Brokerage  > Savings THEN Brokerage
    END
FROM CustomerAccounts;

 

We could alternatively use CROSS APPLY: 

SELECT ca.CustomerID, MAX(T.Balance) as GreatestBalance
FROM CustomerAccounts as ca
CROSS APPLY (VALUES (ca.Checking),(ca.Savings),(ca.Brokerage)) AS T(Balance)
GROUP BY ca.CustomerID;

 

Other valid approaches include user-defined functions (UDFs) and subqueries with aggregates.

However, as the number of columns or expressions increases, so does the tedium of constructing these queries and the lack of readability and maintainability.

 

With GREATEST, we can return the same results as the queries above with the following syntax:

SELECT CustomerID, GREATEST(Checking, Savings, Brokerage) AS GreatestBalance 
FROM CustomerAccounts;

 

Here is the result set:

CustomerID  GreatestBalance
----------- ---------------------
1001            14109.84
1002            97103.43
1003            33194.01
1004           203100.52

(4 rows affected)

 

Similarly, if you previously wished to return a value that’s capped by a certain amount, you would need to write a statement such as:

DECLARE @Val INT = 75;
DECLARE @Cap INT = 50;
SELECT CASE WHEN @Val > @Cap THEN @Cap ELSE @Val END as CappedAmt;

 

With LEAST, you can achieve the same result with:

DECLARE @Val INT = 75;
DECLARE @Cap INT = 50;
SELECT LEAST(@Val, @Cap) as CappedAmt;

 

The syntax for an increasing number of expressions is vastly simpler and more concise with GREATEST and LEAST than with the manual alternatives mentioned above

As such, these functions allow developers to be more productive by avoiding the need to construct lengthy statements to simply find the maximum or minimum value in an expression list.

 

Common use cases

 

Constant arguments

One of the simpler use cases for GREATEST and LEAST is determining the maximum or minimum value from a list of constants:

SELECT LEAST ( '6.62', 33.1415, N'7' ) AS LeastVal;

 

Here is the result set. Note that the return type scale is determined by the scale of the highest precedence argument, in this case float.

LeastVal
--------
6.6200

(1 rows affected)

 

Local variables

 

Perhaps we wish to compare column values in a WHERE clause predicate against the maximum value of two local variables:

CREATE TABLE dbo.studies (
    VarX varchar(10) NOT NULL,
    Correlation decimal(4, 3) NULL
);

INSERT INTO dbo.studies VALUES ('Var1', 0.2), ('Var2', 0.825), ('Var3', 0.61);
GO

DECLARE @PredictionA DECIMAL(2,1) = 0.7;
DECLARE @PredictionB DECIMAL(3,2) = 0.65;

SELECT VarX, Correlation
FROM dbo.studies
WHERE Correlation > GREATEST(@PredictionA, @PredictionB);
GO

 

Here is the result set: 

VarX       Correlation
---------- -----------
Var2              .825

(1 rows affected)

 

Columns, constants and variables

 

At times we may want to compare columns, constants and variables together. Here is one such example using LEAST:

CREATE TABLE dbo.products (
    prod_id INT IDENTITY(1,1),
    listprice smallmoney NULL
);

INSERT INTO dbo.products VALUES (14.99), (49.99), (24.99);
GO

DECLARE @PriceX smallmoney = 19.99;

SELECT LEAST(listprice, 40, @PriceX) as LeastPrice
FROM dbo.products;
GO

 

And the result set:

LeastPrice
------------
     14.99
     19.99
     19.99

 

Summary

 

GREATEST and LEAST provide a concise way to determine the maximum and minimum value, respectively, of a list of expressions.

For full documentation of the functions, see GREATEST (Transact-SQL) – SQL Server | Microsoft Docs and LEAST (Transact-SQL) – SQL Server | Microsoft Docs.

These new T-SQL functions will increase your productivity and enhance your experience with Azure Synapse Analytics.

 

Providing the GREATEST developer experience in Azure is the LEAST we can do.

 

John Steen, Software Engineer
Austin SQL Team