find max value in a row and update new column with the max column name

Question

I have a table like this

number  col1   col2   col3   col4  max
---------------------------------------
  0     200    150    300     80         
 16      68    250    null    55

I want to find max value between col1,col2,col3,col4 in every row and update the last column "max" with the max value column name!

for example in first row max value is 300 the "max" column value will be "col3" result like this:

number   col1   col2   col3    col4   max
------------------------------------------
  0      200    150    300      80    col3
 16       68    250    null     55    col2

How can I do this?

possible duplicate of [SQL MAX of multiple columns?](http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — Murtaza, Sep 24 '14 at 09:33

score 3 · Answer 1 · edited Sep 24 '14 at 09:33

3

QUERY

SELECT *,(
SELECT MAX(n) 
    FROM
    (
        VALUES(col1),(col2),(col3),(col4)
    ) AS t(n)
)  AS maximum_value
FROM #tmp

edited Sep 24 '14 at 09:33

Ullas

11,450
4
33
50

answered Sep 24 '14 at 09:30

arunbabu

279
3
15

score 0 · Answer 2 · answered Sep 24 '14 at 10:06

Update statement

with MaxValues
    as (select [number], [max] = (
          select (
            select max ([n])
              from (values ([col1]) , ([col2]) , ([col3]) , ([col4])) as [t] ([n])
          ) as [maximum_value])
          from [#tmpTable]) 
    update [#tmpTable]
      set [max] = [mv].[max]
      from [MaxValues] [mv]
           join [#tmpTable] on [mv].[number] = [#tmpTable].[number];

assuming number is a key column

Krishnraj Rana · Answer 3 · 2014-09-24T10:15:26.920

SQL Fiddle

Check in SQL Fiddle

Schema

DECLARE @temp table ([number] int NOT NULL, [col1] int, [col2] int, [col3] int, [col4] int, [colmax] int);

INSERT @temp VALUES (0, 200, 150, 300, 80, null), (16, 68, 250, null, 55, null);

Query

SELECT number
    ,(
        SELECT MAX(col) maxCol
        FROM (
            SELECT t.col1 AS col

            UNION

            SELECT t.col2

            UNION

            SELECT t.col3

            UNION

            SELECT t.col4
            ) a
        ) col
FROM @temp t

and the update statement is -

UPDATE tempCol
SET colmax = a.col
FROM (
SELECT (
        SELECT MAX(col) maxCol
        FROM (
            SELECT t.col1 AS col

            UNION

            SELECT t.col2

            UNION

            SELECT t.col3

            UNION

            SELECT t.col4
            ) a
        ) col
FROM tempCol t
) a

find max value in a row and update new column with the max column name

3 Answers3

QUERY

SQL Fiddle

Schema

Query

Linked