Select highest variable - SQL Server Forums

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)

Select highest variable

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2009-06-30 : 09:48:30

I have a number of int variables. Let's say @var1, @var2, @var3

Is there a straightforward way of selecting whichever has the highest value?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 09:58:45

There is arithmetic mean to get highest value of the variables, but to get the name of the variables holding highest value, use CASE

CASE
WHEN @var1 > @var2 AND @var3 > @var3 then '@var1'
WHEN @var2 > @var1 AND @var2 > @var3 then '@var2'
WHEN @var3 > @var1 AND @var3 > @var2 then '@var3'
END

N 56°04'39.26"
E 12°55'05.63"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-30 : 10:02:27

quote:
Originally posted by Peso

There is arithmetic mean to get highest value of the variables, but to get the name of the variables holding highest value, use CASE

CASE
WHEN @var1 > @var2 AND @var3 > @var3 then '@var1'
WHEN @var2 > @var1 AND @var2 > @var3 then '@var2'
WHEN @var3 > @var1 AND @var3 > @var2 then '@var3'
END

N 56°04'39.26"
E 12°55'05.63"

Can you post an example using Arithmetic Mean just curious !

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 10:17:02

[code]declare @columna int, @columnb int
select @columna = 19, @columnb = 12

SELECT (0.5 * (@ColumnA + @ColumnB + ABS(@ColumnA - @ColumnB))) AS Highest,
(0.5 * (@ColumnA + @ColumnB - ABS(@ColumnA - @ColumnB))) AS Lowest[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources