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

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

If you have multiple columns in a single table, such that comparing them (or determining a maximum) makes sense, it's usually a sign that the data model is incorrect - these values belong in a *single* column (with separate rows for each value) - whether that's part of the current table or a separate table depends on what other columns are present. — Damien_The_Unbeliever, Jul 29 '13 at 09:26
This has already been asked: [SQL MAX of multiple columns?](http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — Andriy M, Aug 06 '13 at 07:35

Nick Krasnov · Answer 1 · 2013-08-02T09:50:28.007

4

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

edited Aug 02 '13 at 09:50

answered Jul 27 '13 at 11:44

Nick Krasnov

26,886
6
61
78

Whoa, I've entirely missed that constructor. Nifty! :) – Joachim Isaksson Jul 27 '13 at 11:48

score 3 · Answer 2 · edited Jan 18 '21 at 12:30

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;

score 1 · Answer 3 · answered Jul 27 '13 at 11:33

You have labelled this with two entirely different databases. In Oracle, you do:

select greatest(col1, col2, col3, . . .)

SQL Server doesn't have a greatest() function, so you can use a giant case statement:

(case when col1 >= col2 and col1 >= col3 and col1 >= . . . then col1
      when col2 >= col3 and col2 >= . . .
      . . .
 end)

By the way, in both cases, you have to be careful if the column can contain NULL values.

score 1 · Answer 4 · edited Aug 06 '13 at 07:33

1

This may not be the best solution but it works as expected (on SQL Server 2005+):

DECLARE @x xml= N''
SELECT t.col1, t.col2, t.col3, t.col4,
       @x.value('fn:max((sql:column("t.col1"), 
                         sql:column("t.col2"), 
                         sql:column("t.col3"), 
                         sql:column("t.col4")))', 'int')
FROM dbo.test140 t

See demo on SQLFiddle

edited Aug 06 '13 at 07:33

Andriy M

76,112
17
94
154

answered Jul 29 '13 at 08:47

Aleksandr Fedorenko

16,594
6
37
44

I did some testing and the performance seems to be very poor. Even worse than sub query with max(). – jumxozizi Jan 25 '18 at 13:06

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

4 Answers4