greatest value

[sepia]

Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.

 

[gmmastros]

Since you're only talking about 3 columns, I would probably use the case syntax, like so...

Declare @Temp Table(Val1 Integer, Val2 Integer, Val3 Integer)

Insert Into @Temp Values(1,2,3)
Insert Into @Temp Values(7,2,3)
Insert Into @Temp Values(1,8,3)
Insert Into @Temp Values(1,2,9)
Insert Into @Temp Values(null,2,3)
Insert Into @Temp Values(1,2,null)

Select Case When Val3 > Case When Val1 > Val2 Then Val1 Else Val2 End
			Then Val3
			Else Case When Val1 > Val2 Then Val1 Else Val2 End
			End As GreatestOne
From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Me too... 3 is the limit.

Anyway:

create table blah (PK int, name varchar(30), col1 int, col2 int, col3 int)
insert into blah values (1, 'foo', 1, null, 2)
insert into blah values (2, 'bar', 4, 5, 3)

select PK, name, max(Col1) as max123
from
(	select PK, name, Col1 from blah 
	union all
	select PK, name, Col2 from blah
	union all
	select PK, name, Col3 from blah
) X
group by PK, name

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[sepia]

Hmmm. Never considered the case statement. Thank you very much, guys.