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.