Compare Snowflake vs SQL Server GREATEST and LEAST Functions

By: Sergey Gigoyan   |   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;

The LEAST function returns the minimum value:

SELECT LEAST(10, 5, 8, 20, 15) AS min_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;

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.

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

SELECT @@VERSION

SELECT GREATEST(10, 5, 8, 20, 15) AS max_value;
 
SELECT LEAST(10, 5, 8, 20, 15) AS min_value;

Here is a SQL Server 2022 instance.

SELECT @@VERSION

SELECT GREATEST(10, 5, 8, 20, 15) AS max_value;
 
SELECT LEAST(10, 5, 8, 20, 15) AS min_value;

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;

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

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

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:

• GREATEST (Transact-SQL) - SQL Server | Microsoft Learn
• GREATEST | Snowflake Documentation
• Find MAX value from multiple columns in a SQL Server table (mssqltips.com)
• PostgreSQL: Documentation: 15: 9.18. Conditional Expressions
• GREATEST (oracle.com)
• MySQL :: MySQL 8.0 Reference Manual :: 12.4.2 Comparison Functions and Operators

About the author

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