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

Tweet