You are currently viewing Max or min value from a set of columns
Microsoft SQL Server

Max or min value from a set of columns

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.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

Leave a Reply