Introducing the GREATEST and LEAST T-SQL functions in Azure Synapse Analytics
Published Apr 22 2021 03:07 PM 5,776 Views

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.




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 =
        WHEN Checking >= Savings and Checking >= Brokerage THEN Checking
        WHEN Savings > Checking and Savings > Brokerage THEN Savings
        WHEN Brokerage > Checking and Brokerage  > Savings THEN Brokerage
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.


(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);

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);


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);

DECLARE @PriceX smallmoney = 19.99;

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


And the result set:





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

1 Comment
Version history
Last update:
‎Sep 15 2021 12:03 PM
Updated by: