Return ColumnName from Max Value in columns

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 2008 Forums

Transact-SQL (2008)

Return ColumnName from Max Value in columns

Author

Topic

magefesa
Starting Member

4 Posts

Posted - 2011-03-17 : 06:19:54

Hi sirs !

I have a Table like this (to simplify) :

COL1 | COL2 | COL3 | COL4 |... | COL24 | COL_MAX_COL
12 | 23 | 21 | 2 |... | 4 | (value to fill)

I need a query to recover the max value (in the sample is 23) and then NAME OF THE COLUMN (in the sample COL2), with the columnname, i would like to UPDATE that row in the COL_MAX_COL with the columnName returned.

Can somebody help me ??? I'm tired...seraching a lot in google and no results... I 've achieved to recover the max value (using a user defined function), but no method to recover the column...

Thanks in advence for your great support !!

Frank

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 07:13:22

DECLARE @Table Table (Col1 int,Col2 int,Col3 int,Col4 int)
INSERT INTO @table
SELECT 1,6,8,24
SELECT *
FROM
(

select Col,ColNames,rank() over(order by ColNames desc) as Biggest
from
(select col1,col2,col3,col4
from @table
)pvt
UNPIVOT
(ColNames FOR Col in (Col1,Col2,Col3,Col4)
) as unpvt

) t

WHERE biggest = 1

Jim

Everyday I learn something that somebody else already knew

magefesa
Starting Member

4 Posts

Posted - 2011-03-17 : 07:33:19

Thanks sir !!!! I'll try today at home with the "big database", but sample is really great !!

Thanks from Barcelona ! !!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-17 : 07:36:55

De Nada!

Jim

Everyday I learn something that somebody else already knew

magefesa
Starting Member

4 Posts

Posted - 2011-03-17 : 08:08:47

jajajaja... nice spanish !!

magefesa
Starting Member

4 Posts

Posted - 2011-03-17 : 08:19:36

Let me "abuse"...

I want this Query to run over each row in the table and search for column with max value, once i 've got it, i would like to update a column of that row with the columnname retrieved.... This SP is failing ??Any help please ?

declare @max as float
declare @id as int
declare @fecha as date

declare CURSORITO cursor for
select id,dbo.Max2( [0], dbo.Max2( [1], dbo.Max2( [2], [3] ) ) )

open CURSORITO

fetch next from CURSORITO
into @id,@max
while @@fetch_status = 0
begin
SELECT *
FROM
(
select Col,ColNames,rank() over(order by ColNames desc) as Biggest
from
(select [0],[1],[2],[3]
from t_linies2011 where data=@fecha
)pvt
UNPIVOT
(ColNames FOR Col in ([0],[1],[2],[3])
) as unpvt
) t
WHERE biggest = 1

GO
update T_linies2011 set hmax= @max where id=@id

fetch next from CURSORITO
into @id, @max
end
-- cerramos el cursor
close CURSORITO
deallocate CURSORITO

Thanks again !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-18 : 03:47:41

Also refer method 3
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources