Calculation Question - 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 2000 Forums

SQL Server Development (2000)

Calculation Question

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2006-10-13 : 11:46:44

Is there an easy way to find the highest value in a list with a procedure, without programming a whole lot of conditional statements? The numbers will eventually be dynamic and eventually increase to 5 instead of 3, I'm just trying this with static variables right now. Also, what if two of the numbers equal. Can I easily pick one programmatically, without more conditional statements?

Declare @Number1 int
Declare @Number2 int
Declare @Number3 int

Set @Number1 = 100
Set @Number2 = 101
Set @Number3 = 103

IF @Number1 > @Number2 AND @Number1 > @Number3
BEGIN
Select @Number1
END

IF @Number2 > @Number1 AND @Number2 > @Number3
BEGIN
Select @Number2
END

IF @Number3 > @Number1 AND @Number3 > @Number2
BEGIN
Select @Number3
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 11:51:16

select max(d.number)
from (select @number1 number union all
select @number2 union all
select @number3 union all
select @number4 union all
select @number5) d

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 12:05:05

if you want to stick with your old syntax, try this

declare @max int
select @max = @number1

if @number2 > @max
  select @max = @number2

if @number3 > @max
  select @max = @number3

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 12:12:56

[code]
sekect
case
when @Number1 > @Number2 AND @Number1 > @Number3
then @Number1
when @Number2 > @Number3
then @Number2
else @Number3
end

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources