SQL: GREATEST and LEAST - awesome new additions to Azure SQL Database

I still get really excited when T-SQL has new language elements added. I’m not talking about new data definition or management statements. I’m talking about new functions and logic. So, as expected, I was really happy when I saw some posts saying that GREATEST and LEAST now seemed to be working in Azure SQL Database.

They’ve been in other database engines before but weren’t part of T-SQL.

I tried them, and sure enough, they work as expected. NOTE: Intellisense hasn’t caught up yet, so they’re still flagged as syntax errors.

GREATEST and LEAST

GREATEST takes a list of values and returns the largest. LEAST takes a list of values and returns the smallest.

Note that both ignore NULL values, and unlike aggregates, don’t produce any message about the NULL value being ignored either:

That’s how you’d hope it would work.

This is really good news. It’s so common to want to choose values on this basis. For example, if I want to offer a customer the lowest price i.e. either their own special price, a sale price, or the normal price:

SELECT LEAST(pp.SalePrice, pp.Price, cp.CustomerPrice) AS PriceToCharge
FROM Sales.ProductPrices AS pp
LEFT OUTER JOIN SalesCustomerPrices AS cp
ON cp.ProductID = pp.ProductID 
AND cp.CustomerID = @CustomerID
WHERE pp.ProductID = @ProductID

The Old Way

Before these functions existed, doing the above either required a convoluted bunch of CASE statements or a potentially even less obvious OUTER APPLY statement:

SELECT MIN(pr.Price) AS PriceToCharge
FROM Sales.ProductPrices AS pp
LEFT OUTER JOIN SalesCustomerPrices AS cp
ON cp.ProductID = pp.ProductID 
AND cp.CustomerID = @CustomerID
OUTER APPLY
(
    VALUES(pp.SalePrice), (pp.Price), (cp.CustomerPrice) AS p(Price)
) AS pr
WHERE pp.ProductID = @ProductID

And that would get messier and messier if you needed to do it for multiple columns.

This is really good news. Thank you SQL Server team. Hope it comes to an on-premises version soon.

2021-01-12