Blog

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:

Returning 4, 5 and 6 in this case:

SELECT MAX(Value1), MAX(Value2), MAX(Value3) FROM dbo.DemoTable

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

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:

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