C styled min/max in SQL?

Question

Possible Duplicate:
Getting the minimum of two values in sql

I'm trying to get all values between a range. I'm doing something like this

DECLARE @StartSeq INT
set @StartSeq = (select Sequence from MyTbl where ...)

update ... and MyCol between @StartSeq and @StartSeq+@RelativePosition

But if RelativePosition is negative it fails because its a smaller amount then startseq. The easiest solution I can think of is duplicate my code and doing an if (RelPos<0) to see if I want to do @StartSeq+@RelativePosition first or second. However that doesn't seem like the best solution. Is there a way i can do it in sql? I tried min(a,b) but it doesnt work in sql.

score 4 · Accepted Answer · answered Jan 07 '13 at 14:53

something like this would do:

update ... and ((MyCol between @StartSeq and @StartSeq+@RelativePosition)
             or (MyCol between @StartSeq+@RelativePosition and @StartSeq))

alternately:

declare @MinSeq int
declare @MaxSeq int

select @MinSeq = min(Seq), @MaxSeq = max(Seq)
from (values (@StartSeq), (@StartSeq+@RelativePosition)) this (Seq)

update ... and MyCol between @MinSeq and @MaxSeq

score 0 · Answer 2 · answered Jan 07 '13 at 14:52

You can use a CASE statement in your between, E.g.

and MyCol Between CASE When @StartSeq <= @relativePosition Then @Start Else @RelativePosition End
               And CASE When @StartSeq <= @relativePosition Then @RelativePosition Else @Start End

it's rather nasty however, you should really try to ensure that the parameters make sense.

C styled min/max in SQL?

2 Answers2