SQL MAX from multiple columns in a row

Question

I need to fetch the maximum value from multiple columns in a single row. The row looks like this:

Col1 || Col2 || Col3 || Col4 ||
100  || 120  || 130  || 140  ||
100  || 130  || 130  || 140  ||
100  || 140  || 130  || 140  ||

I need to fetch max of these columns where col2=120 so that the result is returned as col4 value which is 140

Add a few more rows with sample data, and also specify the expected result. — jarlh, Jul 05 '16 at 08:53

score 3 · Answer 1 · answered Jul 05 '16 at 08:57

3

This is using MSSQL Server

SELECT
  (
      SELECT Max(v) 
       FROM (VALUES (Col1), (Col2), (Col3),(Col4)) AS value(v) 
   ) as [MaxDate]
FROM [TableName] 
WHER Col2 = 120

answered Jul 05 '16 at 08:57

Anonymous Duck

2,942
1
12
35

What does 'value(v)' mean ? What kind of alias is that ? – Greg Sep 13 '17 at 12:43
Please add more details to this answer. – Cesar Flores Feb 22 '23 at 16:23
@CesarFlores i will leave that to the reader. Learning is not giving all answers. From the query, it is easy to understand. – Anonymous Duck Mar 09 '23 at 12:49

score 0 · Answer 2 · answered Jul 05 '16 at 08:55

0

Since it's only 4 columns, one way would be to simply use case:

SELECT CASE WHEN Col1 >= Col2 AND Col1 >= Col3 AND Col1 >= Col4 THEN Col1
            WHEN Col2 >= Col1 AND Col2 >= Col3 AND Col2 >= Col4 THEN Col2
            WHEN Col3 >= Col1 AND Col3 >= Col2 AND Col3 >= Col4 THEN Col3
            WHEN Col4 >= Col1 AND Col4 >= Col2 AND Col4 >= Col3 THEN Col4
        END As Max
FROM TableName
WHERE Col2 = 120

answered Jul 05 '16 at 08:55

Zohar Peled

79,642
10
69
121

1

As long as there are no NULL's in the table. – jarlh Jul 05 '16 at 08:56
Any generic approach as its just an example, columns may vary for various tables – Ankur M Jul 05 '16 at 08:57

SQL MAX from multiple columns in a row

2 Answers2