SQL SERVER – Finding Max Value From Multiple Values

Here is a question which I have received a few days ago. It was about finding the max value from multiple values without a long CASE statement.

“I have three different variables, I want to find out which one of them has the maximum or highest value. How can I do that?

I know I can do this with the IF or CASE statement, but that makes me write a long chode and I have to manually implement logic.

Is there any other way? “

Absolutely, here is the simple example which will work with SQL Server 2008 and later versions.

Example 1: With Variables

DECLARE @Int1 INT = 1, @Int2 INT = 3, @Int3 INT = 5;
SELECT MAX(v)
FROM (VALUES (@Int1), (@Int2), (@Int3)) AS value(v);

Example 2: Max Value From Multiple Values (Static)

SELECT MAX(v)
FROM (VALUES (1),(5),(3)) AS value(v);

Example 3: With Columns

CREATE TABLE SampleTable
( ID INT PRIMARY KEY, 
Int1 INT,
Int2 INT,
Int3 INT);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (1, 1, 2, 3);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (2, 3, 2, 1);
INSERT INTO SampleTable (ID, Int1, Int2, Int3)
VALUES (3, 1, 3, 2);
-- Query to select maximum value
SELECT ID,
(SELECT MAX(v)
FROM (VALUES (Int1), (Int2), (Int3)) AS value(v)) AS MaxValue
FROM SampleTable;

I hope these simple queries help you find the max value from multiple values, whether they are variables, constants or columns.

The Modern Way: GREATEST in SQL Server 2022

This VALUES trick still works well on every version since SQL Server 2008. If you are on SQL Server 2022 or Azure SQL Database, there is now a built-in function for exactly this job. GREATEST returns the largest value from its list, and LEAST returns the smallest one. No more nested CASE expressions, which get hard to read after three or four values.

DECLARE @Int1 INT = 1, @Int2 INT = 3, @Int3 INT = 5;
SELECT GREATEST(@Int1, @Int2, @Int3) AS MaxValue,
       LEAST(@Int1, @Int2, @Int3) AS MinValue;

It works with columns too, so Example 3 becomes one short expression per row. A small note on NULL values. MAX in the VALUES method ignores NULL, and GREATEST also ignores NULL unless every argument is NULL. So for simple numbers like these, both methods give the same answer. Keep all the arguments of the same data type to avoid surprises from implicit conversion. Before you replace the old code, check the version of every server where your script will run, because GREATEST does not exist in older versions. For those servers, keep the VALUES method, and one script keeps working everywhere.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.