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.
As an example, let’s say we have a table CustomerAccounts and wish to return the maximum account balance for each customer:
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
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