return name of column holding highest value

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)

return name of column holding highest value

Author

Topic

bpregler
Starting Member

11 Posts

Posted - 2008-04-25 : 15:24:11

I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated

select clientID,
(select max(incomeValue)
from
(select earnings as incomeValue
union all
select unemployment
union all
select pensionRetirement
union all
select alimony
union all
select childSupport
union all
select dividendInterest
union all
select SS
union all
select SSI
union all
select SSDI
union all
select veteranBenefits
union all
select FIP
union all
select workStudy
union all
select other
union all
select otherHHWS) as income) as MaxIncomeValue
from tbl_income

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 16:27:31

Have you tried UNPIVOT?

E 12°55'05.25"
N 56°04'39.16"

bpregler
Starting Member

11 Posts

Posted - 2008-04-25 : 16:30:08

i dont know all the new 2005 functionality yet. but i dont see how unpivot is going to give me the column name.(example result set)

clientId primaryIncome
1 earnings
2 unemployment
3 SSI
4 SSI
5 SSDI

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 16:39:46

If you want multiple records for a Client when there's a tie between two types or more of IncomeType, replace ROW_NUMBER() with RANK() or DENSE_RANK().

SELECT	ClientID,
	IncomeValue,
	theColumn
FROM	(
		SELECT	i.ClientID,
			p.IncomeValue,
			p.theColumn,
			ROW_NUMBER() OVER (PARTITION BY i.ClientID ORDER BY p.IncomeValue DESC) AS RecID
		FROM	tbl_Income AS i
		UNPIVOT	(
				IncomeValue
				FOR theColumn IN (i.[Earnings], i.[Unemployment], i.[PensionRetirement], i.[Alimony], i.[ChildSupport], i.[DividendInterest], i.[SS], i.[SSI], i.[SSDI], i.[VeteranBenefits], i.[FIP], i.[WorkStudy], i.[Other]) 
			) AS p
	) AS e
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 16:44:40

If you want all incomes ordered, try this.
And please read Books Online before you decide something will not work of which you have no knowledge.


SELECT	i.ClientID,
	p.IncomeValue,
	p.theColumn,
	ROW_NUMBER() OVER (PARTITION BY i.ClientID ORDER BY p.IncomeValue DESC) AS RecID
FROM	tbl_Income AS i
UNPIVOT	(
		IncomeValue
		FOR theColumn IN (i.[Earnings], i.[Unemployment], i.[PensionRetirement], i.[Alimony], i.[ChildSupport], i.[DividendInterest], i.[SS], i.[SSI], i.[SSDI], i.[VeteranBenefits], i.[FIP], i.[WorkStudy], i.[Other]) 
	) AS p

Why do you ask for help of you don't trust us?

E 12°55'05.25"
N 56°04'39.16"

bpregler
Starting Member

11 Posts

Posted - 2008-04-25 : 16:52:59

both your examples gave the same error
The multi-part identifier "i.ClientID" could not be bound.

i trust i just dont think you understand what i want as output

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 16:55:28

My bad. Replace both i.ClientID with p.ClientID

E 12°55'05.25"
N 56°04'39.16"

bpregler
Starting Member

11 Posts

Posted - 2008-04-25 : 16:58:30

you the man peso, and fyi, i have a two day seminar on tuesday and wednes day for the new features of 2005. thanks again

bpregler
Starting Member

11 Posts

Posted - 2008-04-25 : 17:04:59

ok now i want to take the results of this query and update a column in my database.

update tbl_Income
set primaryIncome = theColumn

somehow using this:

SELECT ClientID,
theColumn
FROM (
SELECT p.ClientID,
p.theColumn,
ROW_NUMBER() OVER (PARTITION BY p.ClientID ORDER BY p.IncomeValue DESC) AS RecID
FROM tbl_Income AS i
UNPIVOT (
IncomeValue
FOR theColumn IN (i.[Earnings], i.[Unemployment], i.[PensionRetirement], i.[Alimony], i.[ChildSupport], i.[DividendInterest], i.[SS], i.[SSI], i.[SSDI], i.[VeteranBenefits], i.[FIP], i.[WorkStudy], i.[Other])
) AS p
) AS e
WHERE RecID = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:09:56

quote:
Originally posted by bpregler

you the man peso, and fyi, i have a two day seminar on tuesday and wednes day for the new features of 2005. thanks again

Thanks.

I hope the seminar goes well. And you probably should change your attitude a little

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:13:57

Someting similar to this

update		ti
set		ti.primaryIncome = y.theColumn
FROM		tbl_Income AS ti
inner join	(
			SELECT	ClientID,
				theColumn
			FROM	(
					SELECT	p.ClientID,
						p.theColumn,
						ROW_NUMBER() OVER (PARTITION BY p.ClientID ORDER BY p.IncomeValue DESC) AS RecID
					FROM	tbl_Income AS i
					UNPIVOT	(
							IncomeValue
							FOR theColumn IN (i.[Earnings], i.[Unemployment], i.[PensionRetirement], i.[Alimony], i.[ChildSupport], i.[DividendInterest], i.[SS], i.[SSI], i.[SSDI], i.[VeteranBenefits], i.[FIP], i.[WorkStudy], i.[Other]) 
						) AS p
				) AS e
			WHERE	RecID = 1
		) AS y ON y.ClientID = ti.ClientID

E 12°55'05.25"
N 56°04'39.16"

bpregler
Starting Member

11 Posts

Posted - 2008-05-06 : 10:55:18

Funny you are the first person ever who thinks i have a bad attitude. it must be an american european thing i guess. but Thanks for all the help you gave me i appreciate it. and the seminar was wonderful

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:17:15

I was refering to your statement
"but I don't see how unpivot is going to give me the column name."

without even trying the suggestion. Especially even when you also wrote that you did not have enough knowledge, yet.
I am happy your seminar went well!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources