GREATEST / LEAST Function Alternatives in SQL Server

One of the most upvoted requests on feedback.azure.com is for Microsoft to add MAX/MIN as non-aggregate functions in SQL Server. In other words, the GREATEST and LEAST functions that we have been able to find in MySQL, PostgreSQL, and Oracle for a while.

These functions are fairly simple in nature. They’ll return the highest or lowest value from a list of values or fields. In other words, the greatest or least value out of values on the same row. So it’s a bit of a mystery why something so simple and readily available in other RDBMSes hasn’t been included in SQL Server…until now…kinda.

As of 4/14/21, Microsoft has officially announced that the GREATEST and LEAST functions are in Azure SQL Database and SQL Managed Instance. Unofficially, it seems they had silently included these functions in at least (no pun intended) SQL Managed Instance several months prior. In any case, here we are today with official Microsoft documentation on GREATEST and LEAST. This is all great news. What’s also great is that, in their statement, Microsoft stated they would be including these two functions in the next version of SQL Server.

But what about all of us on SQL Server 2019 and prior? Fortunately, there’s a way to mimic these two functions in your queries using a correlated subquery in the SELECT clause.

In the simplest form with just a list of static values, you can use this syntax:

SELECT (SELECT MAX(val) FROM (VALUES (10), (30), (20), (5)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (10), (30), (20), (5)) val_tbl(val)) AS Least;

Here’s an example querying a table of values:

SELECT t.val1,
       t.val2,
       t.val3,
       (SELECT MAX(val) FROM (VALUES (t.val1), (t.val2), (t.val3)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (t.val1), (t.val2), (t.val3)) val_tbl(val)) AS Least
FROM   (VALUES (151,275,179),
               (64,268,240),
               (216,29,156),
               (252,271,200)) t(val1, val2, val3);

Or, if you happen to have the Stack Overflow Database, you can try this example:

SELECT p.Id,
       p.AnswerCount,
       p.CommentCount,
       (SELECT MAX(val) FROM (VALUES (p.AnswerCount), (p.CommentCount)) val_tbl(val)) AS Greatest,
       (SELECT MIN(val) FROM (VALUES (p.AnswerCount), (p.CommentCount)) val_tbl(val)) AS Least
FROM   Posts p;

While it would’ve been great to have had these functions all along, I’m happy they’re on their way and that there’s a viable alternative in the meantime.

Leave a Reply Cancel reply