Thursday, September 10, 2009

How to find maximum/minimum value across multiple columns in a single row

In some situations you need to find a maximum/minimum
or average value from more than 2 columns in a table.
We can use a subquery with UNION to do this.

For example:
CREATE TABLE [dbo].[TestTable]
(
[RowID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Value1] [int] NOT NULL,
[Value2] [int] NOT NULL,
[Value3] [int] NOT NULL
)
GO
INSERT INTO TestTable(Name,Value1,Value2,Value3)
SELECT 'FirstRow',1,2,3
UNION ALL
SELECT 'SecondRow',2,1,7
UNION ALL
SELECT 'ThirdRow',8,9,5
GO

RowID    Name     Value1        Value2       Value3
-----     ----------        -----------     -----------     -----------
   1         FirstRow      1               2                 3
   2        SecondRow  2               1                 7
   3        ThirdRow      8               9                 5

Here is the query:

SELECT *,( SELECT MAX([Value])
                  FROM ( SELECT Value1 AS [Value]
                               UNION ALL
                              SELECT Value2
                              UNION ALL
                              SELECT Value3
                           ) AS UnionSet
                ) AS MaxValue
FROM TestTable
GO

1 comment:

  1. Thanks, this was very helpful. I had to do something similar but I also need to add a field that shows the column name in a seperate field for the MaxValue. Any advise would be very helpful.

    Example:

    Value1 Value2 Value3 MaxValue FieldName
    1 2 3 3 Value3
    2 1 7 7 Value3
    8 9 5 9 Value2

    ReplyDelete