Click on the banner to learn about and purchase my database training on Azure

SQL Server - The “new” GREATEST and LEAST functions

Views: 2.006
Reading Time: 3 minutes

Speak guys !!
In this post I would like to bring a new feature that is already available in Azure SQL Database and I believe that soon, it will be available in SQL Server On-premises too, which are the “new” (For those who already work with other banks, this function is well known and has been available for many years) GREATEST and LEAST functions, which aim to return the highest or lowest value among a list of different values ​​or columns.

I haven't seen documentation about it yet, nor any content in Portuguese and I decided to share this news and also show how it will work.

I learned about these new features when I saw a tweet from legend Itzik Ben-Gan about it:

I even thought it was a prank or a joke, I tested it on a SQL Server 2019 CU8 (most recent so far) and, as I expected, an error message appeared stating that this function does not exist:

But when I went to test on Azure SQL Database, which always has the most current version of SQL Server possible, that function was there:

So I will quickly demonstrate some ways to use these two functions in SQL Server.

If you are using a version of SQL Server that does not support the GREATEST and LEAST functions, you can achieve the same behavior using some techniques that I explained in the article How to retrieve the largest value between multiple columns in a SQL Server table.

Example:

This can also be applied to multiple columns in a table:

Already using the new functions, the behavior is quite simple:

Remembering that this function works with dates, numbers and even strings

Example with numbers:

Example with words:

Some notes on these functions

  • If the data type of the values ​​or columns differs, all expressions will be converted to the type of the first expression or column for comparison.
  • When comparing strings and texts, the comparison will be made using the ASCII code, that is, the algorithm will compare the first character of each expression and identify which has the largest ASCII code ... In case of a tie, the second character will be evaluated and so successively until all expressions have already been evaluated to decide the highest or lowest.

Well, that's it folks!
I hope you enjoyed the article and even more!