how to get max value with column name in sql

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)

how to get max value with column name in sql

Author

Topic

sql4us
Starting Member

24 Posts

Posted - 2011-03-17 : 17:14:49

I want to get max value with that column name from multiple columns(i have 8 columns) for each row
for ex:
FID col1 col2 col3 col4 col5 col6 col7 col8
99011 4 0 0 2 1 0 3 1
55555 1 2 0 4 1 1 8 0
32443 1 1 1 1 1 1 1 1

I want to see output as

FID MAX Column_name
99011 4 col1
55555 8 col7
32443 1 ? (what will be in this scenario if columns are tied?)

Please let me know how to write store procedure or sql query with if and else statments or any method..

Thanks in advance!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 17:36:53

Here's a proof of concept:

create table #test(FID int, col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, col7 int, col8 int)
insert #test values(99011,4,0,0,2,1,0,3,1)
insert #test values(55555,1,2,0,4,1,1,8,0)
insert #test values(32443,1,1,1,1,1,1,1,1)

;with u(FID, original_col, value, col) AS (
select FID, original_col, value, 'col' + cast(row_number() over (partition by FID order by FID, value desc) as varchar) col
from (
	select FID, col1, col2, col3, col4, col5, col6, col7, col8 from #test) ua 
	unpivot(value for original_col in (col1, col2, col3, col4, col5, col6, col7, col8)) ub)
select FID, value, original_col from u where col='col1'

If columns are tied it really doesn't matter which one it selects. This query returns the lowest column but I don't know if that's consistent or not.

sql4us
Starting Member

24 Posts

Posted - 2011-03-17 : 17:49:57

quote:

Originally posted by robvolk

Here's a proof of concept:

create table #test(FID int, col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, col7 int, col8 int)
insert #test values(99011,4,0,0,2,1,0,3,1)
insert #test values(55555,1,2,0,4,1,1,8,0)
insert #test values(32443,1,1,1,1,1,1,1,1)

;with u(FID, original_col, value, col) AS (
select FID, original_col, value, 'col' + cast(row_number() over (partition by FID order by FID, value desc) as varchar) col
from (
	select FID, col1, col2, col3, col4, col5, col6, col7, col8 from #test) ua 
	unpivot(value for original_col in (col1, col2, col3, col4, col5, col6, col7, col8)) ub)
select FID, value, original_col from u where col='col1'

If columns are tied it really doesn't matter which one it selects. This query returns the lowest column but I don't know if that's consistent or not.

Hey robvolk!

Thank you so much for the reply. I really appreciate.
Actually I have a table with all those columns in it so I need not create table so can you explain me how to query the max values with columname from the table and its above mentioned columns.

Thanks again

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 17:55:23

Ignore everything above the CTE definition, and just change the reference to the #test table to your actual table.

sql4us
Starting Member

24 Posts

Posted - 2011-03-17 : 18:17:03

quote:
Originally posted by robvolk

Ignore everything above the CTE definition, and just change the reference to the #test table to your actual table.

Thanks again!

Is this below query looks correct? Did I miss anything?

select FID, original_col, value, 'col' + cast(row_number() over (partition by FID order by FID, value desc) as varchar) col
from (
select FID, col1, col2, col3, col4, col5, col6, col7, col8 from table1) ua
unpivot(value for original_col in (col1, col2, col3, col4, col5, col6, col7, col8)) ub)
select FID, value, original_col from u where col='col1'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 19:47:20

Looks good to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources