Max Of Three Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Max Of Three Columns

Author

Topic

Lac0niC
Starting Member

5 Posts

Posted - 2008-01-23 : 03:55:06

I have four columns that include numbers like: A B C D

I want to update D with highest value of A, B or C.

For example A = 1, B = 2, C= 3 then D should be 3.

Can you help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 04:02:08

UPDATE t
SEt t.D =tmp.Value
FROM Table t
CROSS APPLY (SELECT MAX (t.Value) AS Value FROM
(SELECT A AS Value FROM Table WHERE pk=t.pk
UNION ALL
SELECT B AS Value FROM Table WHERE pk=t.pk
UNION ALL
SELECT C AS Value FROM Table WHERE pk=t.pk)t
)tmp

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-01-23 : 04:03:27

[code]
Update TableName Set D =
Case When A>B And A> C Then A --When A is the higest..
When B>A And B>C Then B --When B is higesh..
Else C
End --Finally C..

[/code]

Something like this ?

Chirag

http://www.chirikworld.com

Lac0niC
Starting Member

5 Posts

Posted - 2008-01-23 : 04:25:48

Thank both of you. Second one works for me.

I'm using SQL 2000 so first one didn't worked, i think. Sorry, i didn't say it and sent post to 2005 forum.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 04:36:10

create table #Temp
(
valA int,
valB int,
valC int,
valD int
)

Insert into #Temp values (1,2,3,0)

UPDATE #Temp
SET #Temp.valD=C.Col1
FROM (select Max([COL])[Col1] from
(
select MAX(valA) [COL] from #Temp union
select MAX(valB) [COL] from #Temp union
select MAX(valC) [COL] from #Temp
)
B ) c

select * from #Temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-23 : 07:22:50

or

UPDATE @temp
SET valD=C.Col1
FROM (select Max([COL])[Col1] from
(
select (valA) [COL] from @temp union all
select (valB) [COL] from @temp union all
select (valC) [COL] from @temp
)
B ) c

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources