Find highest among columns and update col name

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)

Find highest among columns and update col name

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-11-22 : 22:21:16

Hi, my data in table is as follows


Zip  White1 White2  White3  Whitehigh  Blac1  Blac2  Blac3  Blachigh
123  2      6        65      null        4     5      7      null
345  6      7        6       null        4     3      1      null

My requirement is i want to find out the highest value among white1,White2,White3 and update the columnname in whitehigh column.
i.e If we observe 1st row, White3 is the highest among white and update the same in whitehigh column. Similary black.

I need an update query for the same requirement.

The output i am expecting is as follows


Zip  White1 White2  White3  Whitehigh  Blac1  Blac2  Blac3  Blachigh
123  2      6        65      White3      4     5      7      Blac3
345  6      7        6       White1      4     3      1      Blac1

developer :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-22 : 22:23:01

How about normalizing your table design so that you don't have a structure that's so hard to work with?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 22:39:34

Tara's right. That type of design is going to create lots of problems. just to get ya through the day though...

update	yourTable
set	whitehigh = Case
		When white1 >= white2 and white1 >= white3 Then white1
		When white2 >= white1 and white2 >= white3 Then white2
		Else white3 End

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-11-22 : 23:07:37

I will update the columns as per my requriment and i will remove remaiing columns. i.e the whitehigh column contians the column name of highest value and the remaining columns are not necessary once update was done. i will remove remaiing columns

quote:
Originally posted by tkizer

How about normalizing your table design so that you don't have a structure that's so hard to work with?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

developer :)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 23:12:02

you need the name of it? that's a strange one. in that case wrap single quotes around the result of the case stament i showed:

When white1 >= white2 and white1 >= white3 Then 'white1' etc.

how will you break ties? what is this for? of course a proper design as Tara says would make this whole excercise unnecessary...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-22 : 23:17:31

[code]
select Zip, max(White), max(Black)
from
(
select Zip, White = White1, Black = Blac1 from yourtable union all
select Zip, White = White2, Black = Blac2 from yourtable union all
select Zip, White = White3, Black = Blac3 from yourtable
) z
group by Zip[/code]

KH
[spoiler]Time is always against us[/spoiler]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 23:21:43

nice. but he want the column name...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-22 : 23:27:33

quote:
I will update the columns as per my requriment and i will remove remaiing columns. i.e the whitehigh column contians the column name of highest value and the remaining columns are not necessary once update was done. i will remove remaiing columns

But it does not make sense. The highest value for each record will be different. I think OP just want to retain the highest value of the 3 in a new column and remove the existing 3 columns.

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-11-22 : 23:33:21

Yes exactly. Once updating the Whitehigh,Blackhigh with highest column names, then i will remove remaining columns.

quote:
Originally posted by khtan

quote:
I will update the columns as per my requriment and i will remove remaiing columns. i.e the whitehigh column contians the column name of highest value and the remaining columns are not necessary once update was done. i will remove remaiing columns

But it does not make sense. The highest value for each record will be different. I think OP just want to retain the highest value of the 3 in a new column and remove the existing 3 columns.

KH
[spoiler]Time is always against us[/spoiler]

developer :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-22 : 23:37:06

i think you mean

quote:
Once updating the Whitehigh,Blackhigh with highest column ~~names~~ VALUE

Column Name will be the name of the column like White1, White2 etc

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-11-22 : 23:39:00

Yes, i want to update column name not a value..ie White2 to Whitehigh, Black1 to Blackhigh like that..

quote:
Originally posted by khtan

i think you mean

quote:
Once updating the Whitehigh,Blackhigh with highest column ~~names~~ VALUE

Column Name will be the name of the column like White1, White2 etc

KH
[spoiler]Time is always against us[/spoiler]

developer :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-22 : 23:48:14

So after that you are going to drop column White1, White2, White3 ?

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-11-23 : 00:01:03

yes, exactly

quote:
Originally posted by khtan

So after that you are going to drop column White1, White2, White3 ?

KH
[spoiler]Time is always against us[/spoiler]

developer :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-23 : 00:07:52

So what's good when your column Whitehigh contains value 'White1' or 'White2' but without the value for White1 or White2 ?

KH
[spoiler]Time is always against us[/spoiler]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-23 : 00:34:27

exactly what i was wondering