Compare Snowflake vs SQL Server GREATEST and LEAST Functions

By:   |   Updated: 2023-10-11   |   Comments (4)   |   Related: More > Snowflake


Problem

Finding the maximum or minimum value among provided values or column values is a common task that database developers face. This article will look at solving this problem using the GREATEST and LEAST functions in Snowflake and SQL Server.

Solution

The GREATEST and LEAST functions are available in almost all popular database management systems to get the maximum or minimum value among provided values. In Snowflake, for example, these functions are called conditional expression functions.

LEAST and GREATEST for Snowflake

The following code gets the maximum value among the provided numbers:

SELECT GREATEST(10, 5, 8, 20, 15) AS max_value;
Snowflake-maximum value among the provided numbers

The LEAST function returns the minimum value:

SELECT LEAST(10, 5, 8, 20, 15) AS min_value;
Snowflake-LEAST function returns the minimum value

If we have NULLs in the provided values' list, Snowflake's GREATEST and LEAST functions will return NULL:

SELECT LEAST(10, 5, 8, 20, 15) AS min_value;
 
SELECT GREATEST(10, 5, 8, 20, 15, NULL) AS max_value;
Snowflakes GREATEST and LEAST functions will return NULL
Snowflakes GREATEST and LEAST functions will return NULL

It is worth mentioning that these functions are available in PostgreSQL, Oracle, and MySQL as well.

LEAST and GREATEST for SQL Server

Interestingly, these functions became available only since SQL Server 2022 and are called logical functions. If we try to use these functions in SQL Server 2019 with the latest cumulative update installed, we will receive an error. As of the time of writing this article, the latest cumulative update for SQL Server 2019 was CU22, which is installed in our SQL Server 2019 instance.

latest cumulative update for SQL Server 2019

Let's check the version in SQL Server Management Studio (SSMS) and then run the logical functions:

SELECT @@VERSION
version in SQL Server 2019
SELECT GREATEST(10, 5, 8, 20, 15) AS max_value;
 
SELECT LEAST(10, 5, 8, 20, 15) AS min_value;
SQL Server 2019 Greatest
SQL Server 2019 Least

Here is a SQL Server 2022 instance.

SELECT @@VERSION
SQL Server 2022
SELECT GREATEST(10, 5, 8, 20, 15) AS max_value;
 
SELECT LEAST(10, 5, 8, 20, 15) AS min_value;
SQL Server 2022 Greatest and least

Unlike the corresponding functions in Snowflake, discussed logical functions in SQL Server ignore NULLs and return the minimum or maximum values regardless of the existing NULLs in the list. If all values are NULL, the functions return NULL:

SELECT GREATEST(10, 5, 8, 20, 15, NULL) AS max_value;
 
SELECT LEAST(10, 5, 8, 20, 15, NULL) AS min_value;
 
SELECT LEAST(NULL, NULL) AS min_value;
SQL Server ignore NULLs

LEAST and GREATEST for Other SQL Server Versions

We discussed the topic of finding the maximum/minimum value from multiple columns in SQL Server before in this article: Find MAX value from multiple columns in a SQL Server table (mssqltips.com). These methods are still correct for the SQL Server 2019 and lower versions.

Let's compare the most efficient method described in the previous article with applying the GREATEST function in SQL Server 2022.

Let's create the same test environment:

IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL)
   DROP TABLE ##TestTable
 
CREATE TABLE ##TestTable
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   Name NVARCHAR(40),
   UpdateByApp1Date DATETIME,
   UpdateByApp2Date DATETIME,
   UpdateByApp3Date DATETIME
)
 
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES('ABC', '2015-08-05','2015-08-04', '2015-08-06'),
     ('NewCopmany', '2014-07-05','2012-12-09', '2015-08-14'),
     ('MyCompany', '2015-03-05','2015-01-14', '2015-07-26')

Now, we will include the actual execution plan in SSMS and will run both queries:

--Finding maximum value among columns using GREATEST
SELECT GREATEST(UpdateByApp1Date,UpdateByApp2Date,UpdateByApp3Date) AS LastUpdateDate
FROM ##TestTable
 
--Fastest method of finding maximum value among columns from the previous article
SELECT 
   ID, 
   (SELECT MAX(LastUpdateDate)
      FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate)) 
   AS LastUpdateDate
FROM ##TestTable
SQL Server 2022 - Greatest function

Looking at the execution plans, we can see that the performance of both queries is the same:

execution plans

However, the code is more compact and easier to understand in case of using the GREATEST function.

Conclusion

The GREATEST and LEAST functions provide a compact way to find maximum and minimum values from the list and are available in popular database management systems. In SQL Server, they are available starting with SQL Server 2022. However, the same problem can be solved using alternative methods in older versions of SQL Server.

Next Steps

For additional information, please follow the links below:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-10-11

Comments For This Article




Thursday, October 12, 2023 - 6:35:55 PM - Greg Robidoux Back To Top (91661)
Hi William,

That is not a function it is just a name that is given.

You can make it anything you want UpdateDate or UDate, etc..

SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UDate(LastUpdateDate) )
AS LastUpdateDate
FROM ##TestTable


Thursday, October 12, 2023 - 4:31:56 PM - William Bailey Back To Top (91659)
I'm talking about the UpdateDate() method. I'm not disputing the Values() method.
Where did the UpdateDate(LastUpdateDate) come from and where is that in Table constructors?

Thursday, October 12, 2023 - 1:10:23 PM - Sergey Gigoyan Back To Top (91656)
William Bailey,
It's not a function. The following article can be helpful:
https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/
Also, checking the "Table Value Constructor" syntax can be useful:
https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16
Thanks,
Sergey Gigoyan

Thursday, October 12, 2023 - 7:44:05 AM - William Bailey Back To Top (91654)
Stupid question:
Where did this method come from; UpdateDate(LastUpdateDate).
As long as I've been using Sql Server I've never run across UpdateDate function in T-SQL. I don't see it referenced or defined anywhere before it's being used, which is adding to my confusion.