Hey guys!!
In this post I would like to bring something new that is already available in Azure SQL Database and I believe that it will soon 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 largest or smallest value among a list of different values ​​or columns.

I haven't seen documentation about this 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 some prank or joke, I went to test it on a SQL Server 2019 CU8 (most recent to date) and, as I expected, an error message appeared stating that this function does not exist:

But when I tested on Azure SQL Database, which always has the most current version of SQL Server possible, this 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 among multiple columns in a SQL Server table.

Example:

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)

And this can also be applied to multiple columns of a table:

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 very simple:

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:

SELECT
    *,
    GREATEST(Valor1, Valor2, Valor3, Valor4, Valor5, Valor6) AS GREATEST,
    LEAST(Valor1, Valor2, Valor3, Valor4, Valor5, Valor6) AS LEAST
FROM 
    ##Teste

Example with words:

SELECT
    *,
    GREATEST(Nome1, Nome2, Nome3, Nome4) AS GREATEST,
    LEAST(Nome1, Nome2, Nome3, Nome4) AS LEAST
FROM 
    #Teste

Some observations about these functions
  • If the data type of the values ​​or columns differs, all expressions are converted to the type of the first expression or column to compare.
  • 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 one has the largest ASCII code... In case of a tie, the second character will be evaluated and so on until all expressions have already been evaluated to decide which is the largest or smallest.

Well, that's it folks!
I hope you enjoyed the article and see you later!