Selecting Max Value from multiple columns

Selecting Max Value from multiple columns

Have you ever had 2 columns in a table that you need to select the maximum value from? Not the maximum value from each column, but the biggest value after comparing the columns?

Usually where I have this problem, I’d use a CASE statement, but there’s another way. Let’s setup some test data:

CREATE TABLE SampleData
(
	ID		INT PRIMARY KEY IDENTITY(1, 1)
	,ForeignKey 	INT NOT NULL
	,Value1		INT NOT NULL
	,Value2		INT NOT NULL
)

INSERT INTO SampleData (ForeignKey, Value1, Value2)
VALUES
(1, 1, 2)
,(1, 2, 3)
,(2, 4, 3)
,(2, 5, 4)

So how would we select the biggest value for each row out of Value1 and Value2? Usually I’d just use a case statement:

SELECT	SampleData.ID
	,SampleData.Value1
	,SampleData.Value2
	,CASE WHEN SampleData.Value1 > SampleData.Value2 THEN SampleData.Value1 ELSE SampleData.Value2 END AS Biggest
FROM	SampleData

That certainly works, and it makes a lot of sense to the next developer, but is there another way? Yes! In SQL 2022 there is a new function called GREATEST:

SELECT	SampleData.ID
	,SampleData.Value1
	,SampleData.Value2
	,GREATEST(SampleData.Value1, SampleData.Value2) AS Biggest
FROM	SampleData

But Greg, I’m not on SQL 2022. Is there another way? Well, we could use the trick of selecting from VALUES to essentially pivot the data and then just use a MAX on it:

SELECT	SampleData.ID
	,SampleData.Value1
	,SampleData.Value2
	,(SELECT MAX   (a.i) AS Biggest FROM   (VALUES (Value1), (Value2)) AS a (i) ) AS Biggest
FROM	SampleData

If we want to select the MAX of Value1 or Value2 and group by another column, say the Foreign Key column in our sample data, we would usually do something like this:

SELECT	SampleData.ForeignKey
	,MAX(CASE WHEN SampleData.Value1 > SampleData.Value2 THEN SampleData.Value1 ELSE SampleData.Value2 END)
FROM	SampleData
GROUP BY SampleData.ForeignKey

If we were to do it the new way, we could either use a CROSS APPLY:

SELECT	SampleData.ForeignKey
	,MAX(Biggest.Biggest)
FROM	SampleData
CROSS APPLY (SELECT MAX   (a.i) AS Biggest FROM   (VALUES (Value1), (Value2)) AS a (i) ) AS Biggest
GROUP BY SampleData.ForeignKey

I’m not sure when using the sub select method might be a good option, if you’ve found a use case for it, leave me a note in the comments below.