Identifying Columns with highest value

[JonathanC]

How would you identify the column with the highest value
in a table?

I have this table description
matrix(name,vala,valb,valc,vald,vale,valf,valg,highestval)

i need to somehow assign to highestval which among the columns has the highestvalue, like assigning 'A' if vala has the highest value.

 

[tlbroadbent]

You can use the Case statement in T-SQL. If you are using SQL 2000, I'd recommend converting the code in the case statement to If... Then... Else structure in a User-Defined Function (UDF). You can then call the UDF from the query.

Select
Name,
vala, valb, valc, vald,
vale, valf, valg,
HighestVal=
Case
When vala>=valb
And vala>=valc
And vala>=vald
And vala>=vale
And vala>=valf
And vala>=valg
Then 'A'
When valb>=valc
And valb>=vald
And valb>=vale
And valb>=valf
And valb>=valg
Then 'B'
When valc>=vald
And valc>=vale
And valc>=valf
And valc>=valg
Then 'C'
When vald>=vale
And vald>=valf
And vald>=valg
Then 'D'
When vale>=valf
And vale>=valg
Then 'E'
When valf>=valg
Then 'F'
Else 'G' End
From matrix Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JonathanC]

Thanks TL, I knew this solution, I was just figuring out if there's a shorter way because I have 15 columns and, though possible, I somehow am thinking twice of doing it like that!

But you're the great guy...I guess that's the only option I have so I'll do it that way!

 

[cmmrfrds]

If you don't have a lot of rows, make a temporary table with matrix2(name, value) then insert each value in the table, then select name max(value) from matrix2 group by name.