Find Maximum(table.column1,table.column2,table.column3,...) as 'MaximumValue' from table SQL [duplicate]

Question

SQL (SQL Server)

How to find maximum value among different columns in a single Table Row returned by a SQL Query.

Select Maximum(table.column1,table.column2,table.column3,...) as 'MaximumValue' 
 from table

Answer 1

In SQL Server 2008 and higher you could write a similar query to find greatest value among columns:

select col1
     , col2
     , col3
     , col4
     ,(select max(mx) 
         from (values(col1)
                     ,(col2)
                     ,(col3)
                     ,(col4)) t1(mx)
                ) as MaximumValue
from t1

SQL Fiddle Demo

Answer 2

Solution #1 (only SQL Server 2005+):

DECLARE @MyTable TABLE(Col1 INT,Col2 INT,Col3 INT,Col4 INT);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (1,2,3,4);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (100,20,300,40);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (NULL,NULL,NULL,NULL);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (NULL,1000,NULL,NULL);

SELECT  x.*,z.*
FROM    @MyTable x
CROSS APPLY(
    SELECT  MAX(y.Value) AS MaxOfValue,SUM(y.Value) AS SumOfValue
    FROM(
        SELECT x.Col1 AS Value
        UNION ALL 
        SELECT x.Col2
        UNION ALL 
        SELECT x.Col3
        UNION ALL 
        SELECT x.Col4       
    ) y
) z;

Results:

Col1 Col2 Col3 Col4 MaxOfValue SumOfValue
---- ---- ---- ---- ---------- ----------
1    2    3    4    4          10
100  20   300  40   300        460
NULL NULL NULL NULL NULL       NULL
NULL 1000 NULL NULL 1000       1000

Demo <- click

Solution #2 (SQL Server 2005+, Oracle) SQLFiddle demo(you can switch between SQL Server and Oracle)

SELECT a.*,b.MaxOfValue
FROM MyTable a
LEFT JOIN (
SELECT  unpvt.ID,
        MAX(unpvt.Value) AS MaxOfValue
FROM    MyTable x
UNPIVOT( Value FOR ColumnName IN (Col1,Col2,Col3,Col4) )unpvt
GROUP BY unpvt.ID
) b ON a.ID=b.ID;