Finding the maximum or minimum value among a set of values or column values is a common task in SQL Server. In this article, we will explore how to solve this problem using the GREATEST and LEAST functions in SQL Server.

GREATEST Function

The GREATEST function in SQL Server allows you to find the maximum value among a set of values. Here’s an example:

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

This query will return the maximum value from the provided list, which in this case is 20.

LEAST Function

The LEAST function in SQL Server allows you to find the minimum value among a set of values. Here’s an example:

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

This query will return the minimum value from the provided list, which in this case is 5.

Handling NULL Values

Unlike some other database management systems, the GREATEST and LEAST functions in SQL Server ignore NULL values and return the minimum or maximum values regardless of the presence of NULLs in the list. For example:

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

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

In the above queries, even though NULL is included in the list, the functions will still return the maximum and minimum values respectively.

Alternative Methods in Older Versions

Prior to SQL Server 2022, the GREATEST and LEAST functions were not available. However, there are alternative methods to find the maximum or minimum value from multiple columns. One such method is described in the article “Find MAX value from multiple columns in a SQL Server table” on mssqltips.com.

It’s important to note that the GREATEST and LEAST functions provide a more compact and easier-to-understand solution in SQL Server 2022 and newer versions.

Conclusion

The GREATEST and LEAST functions in SQL Server offer a convenient way to find the maximum and minimum values from a list of values. While these functions are available starting from SQL Server 2022, alternative methods can be used in older versions. By understanding and utilizing these functions, you can efficiently solve the problem of finding the maximum or minimum value in SQL Server.

Article Last Updated: 2023-10-11