The GREATEST and LEAST functions arrive in Azure SQL Database

Towards the end of 2020, Microsoft introduced two brand new TSQL functions to Azure SQL Databases; GREATEST and LEAST which joined Microsoft’s documented list of TSQL functions online this month (GREATEST and LEAST).

Appropriately named, the GREATEST function takes a list of values and returns the largest of these whilst the LEAST takes a list of values and returns the smallest. They really do work as one would expect. Gone are the days of needing to use workaround alternatives such as CROSS APPLYs or UNION ALL to formulate your queries.

It’s worth noting that NULL values are ignored when used with other values and both functions work with various date types including numbers, dates and strings. Let’s walk through some simple examples of these functions in action with different data types

Data Type: INT

SELECT GREATEST(null,1,2,3,4,5)

5

SELECT LEAST(null,1,2,3,4,5)

1

Data Type: Date

SELECT GREATEST('01/01/1900','01/01/2021')

01/01/2021

SELECT LEAST('01/01/1900','01/01/2021')

01/01/1990

Data Type: String

SELECT GREATEST('a','b','c')

c

SELECT LEAST('a','b','c')

a

How does this differ from the existing MAX and MIN functions?

MAX and MIN are aggregate functions that provide you with the greatest/least value in a column. They also require grouping or partitions to run. On the other hand, GREATEST and LEAST are single row expressions for comparisons between several columns or expressions.

You can use MAX and MIN to mimic the functionality that the GREATEST and LEAST functions offer; but it usually involves cross applying any columns to create an aliased single column which adds unnecessary headaches to your code.

Using a mixture of data types

If either function is used when the data type of the values ​​or columns are different; all values will be converted to the data type of highest precedence before the comparison takes place.

For example, in the query below, the SQL engine will attempt to convert each value into an int as this is the data type with the highest precedence:

SELECT GREATEST(1,'A',7)

However, this will fail due to a conversion failure, due to implicate conversion between integers and varchars being unsupported:

Conversion failed when converting the varchar value 'A' to data type int.

Will you be using either of these functions in your coding going forward? Drop a message in the comments below.