Max or min value from a set of columns

Microsoft SQL Server

The min and max aggregates in SQL Server are normally used to work over rows, getting the max or min value from a set of rows. But what if you want to get the max or min value from a set of columns instead?

The following T-SQL creates a table with three integer columns used to demonstrate the solution.

CREATE DATABASE SumColDemo
GO

USE SumColDemo
GO

CREATE TABLE dbo.DemoTable
	(
		Value1 INT NULL
		,Value2 INT NULL
		,Value3 INT NULL
	)
GO

INSERT dbo.DemoTable VALUES (1,2,3),(4,5,6)
GO

SELECT * FROM dbo.DemoTable

Now, a “traditional” max value for instance, generates an aggregate over rows:

Aggregates Over Rows

Aggregates Over Rows

Returning 4, 5 and 6 in this case:

SELECT MAX(Value1), MAX(Value2), MAX(Value3) FROM dbo.DemoTable
Aggregates Over Rows Result

Aggregates Over Rows Result

An aggregate max value from the set of columns however, would return 3 and 6 instead:

Aggregates Over Columns

Aggregates Over Columns

The solution is to use a Table Value Constructor (available from SQL Server 2008):

SELECT
	(SELECT MAX(C) FROM (VALUES (Value1), (Value2), (Value3)) AS T(C)) AS MaxValue
FROM
	dbo.DemoTable

This returns 3 and 6:

Aggregates Over Columns Result

Aggregates Over Columns Result

The downside to this solution is that you’ll have to provide and maintain the list of columns to create the aggregate on.

Leave a Reply

Required fields are marked *.