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:
| 1 2 3 4 5 | SELECT MAX(Valores.Valor) AS [GREATEST], MIN(Valores.Valor) AS [LEAST] FROM (VALUES (4), (85), (120), (154), (52), (87), (999), (15), (12)) AS Valores(Valor) |
This can also be applied to multiple columns in a table:
| 1 2 3 4 5 6 7 8 9 | SELECT ID, Data1, Data2, Data3, ( SELECT MAX(UltimoAcesso) FROM (VALUES (Data1),(Data2),(Data3)) AS UltimoAcesso(UltimoAcesso) ) AS MaiorData FROM ##Teste |
Already using the new functions, the behavior is quite simple:
| 1 2 3 4 5 6 7 | SELECT ID, Data1, Data2, Data3, GREATEST(Data1, Data2, Data3) AS MaiorData, LEAST(Data1, Data2, Data3) AS MenorData FROM ##Teste |
Remembering that this function works with dates, numbers and even strings
Example with numbers:
| 1 2 3 4 5 6 | SELECT *, GREATEST(Valor1, Valor2, Valor3, Valor4, Valor5, Valor6) AS GREATEST, LEAST(Valor1, Valor2, Valor3, Valor4, Valor5, Valor6) AS LEAST FROM ##Teste |
Example with words:
| 1 2 3 4 5 6 | SELECT *, GREATEST(Nome1, Nome2, Nome3, Nome4) AS GREATEST, LEAST(Nome1, Nome2, Nome3, Nome4) AS LEAST FROM #Teste |
- 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!
It is needed.
When I started programming in SQL, accustomed to using general-purpose programming languages, I thought that max () and min () would analyze a set of linear values and then return the highest and lowest value. Something like:
max(8, 3, 5) = 8
I hope that GREATEST and LEAST will also become available on SQL Server.