CODEWITHSUNDEEP
sqlsql-servermax
meysam motamedi
meysam motamedi

Reputation: 75

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

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?

Upvotes: 1

Views: 1239

Answers (3)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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

Upvotes: 0

Thomas Lange
Thomas Lange

Reputation: 1

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

Upvotes: 0

arunbabu
arunbabu

Reputation: 289

QUERY

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

Upvotes: 3

Related Questions