Oracle Least/Greatest functions in T-SQL

Is there an equivilant T-SQL function that will do the same thing as Oracle's Least and Greatest functions.

Ex. Select LEAST(1,2,3,4) --returns 1
Select GREATEST(1,2,3,4) -- returns 4

Thanks

I wouldn't be surprised if you don't see much of a performance boost when using that function instead of a full CASE construct.

But you are right, it looks so much tidier than -

CASE WHEN a < b AND a < c AND a < d THEN a
ELSE (CASE WHEN b < a AND b < c AND b < d THEN b
ELSE (CASE WHEN c < a AND c < b AND c < d THEN c
ELSE d END) END) END

and

CASE WHEN a > b AND a > c AND a > d THEN a
ELSE (CASE WHEN b > a AND b > c AND b > d THEN b
ELSE (CASE WHEN c > a AND c > b AND c > d THEN c
ELSE d END) END) END

Another shot would be -

SELECT MIN(T.c1), MAX(T.c1)
FROM (SELECT a c1 UNION ALL SELECT b UNION ALL SELECT c UNION ALL SELECT b) T

Ah, my mistake: the CASE needs <= or >= instead of just < or >.

Also, if you need to take NULLs into account, use ISNULL on the right part of the comparison: for the <= you need an extremely high replacement value, for the >= an extremely low one.

I didn't make myself clear. What I'm looking for is a least/greatest function that's row based.

For example:

declare @table table (a int, b int, c int, d int)
insert into @table select 1,2,3,4
insert into @table select 4,3,2,1
insert into @table select 400,3000,2,100000
insert into @table select -400,3000,2,100000

Least, Greatest would return a record set like this

Least Greatest
----- --------
1 4
1 4
2 100000
-400 100000

That's where the CASE construct comes in:

declare @table table (a int, b int, c int, d int)
insert into @table select 1,2,3,4
insert into @table select 4,3,2,1
insert into @table select 400,3000,2,100000
insert into @table select -400,3000,2,100000

SELECT
CASE WHEN a <= b AND a <= c AND a <= d THEN /*RESULT:*/ a
ELSE (CASE WHEN b <= a AND b <= c AND b <= d THEN /*RESULT:*/ b
ELSE (CASE WHEN c <= a AND c <= b AND c <= d THEN /*RESULT:*/ c
ELSE /*RESULT:*/ d END) END) END [Least],
CASE WHEN a >= b AND a >= c AND a >= d THEN /*RESULT:*/ a
ELSE (CASE WHEN b >= a AND b >= c AND b >= d THEN /*RESULT:*/ b
ELSE (CASE WHEN c >= a AND c >= b AND c >= d THEN /*RESULT:*/ c
ELSE /*RESULT:*/ d END) END) END [Greatest]
FROM @table

It returns the same results as what you have.

Can't really see a big point in having LEAST or GREATEST as an extension of SQL ... but of course YMMV

Thanks. That'll do it.

That was funny contest [<img src='/community/emoticons/emotion-1.gif' alt='' />]

I missed that, as I was on holiday.[<img src='/community/emoticons/emotion-6.gif' alt='' />]