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

Select min(data) as Least ,max(data) as Greatest from ( Select 1 as data union all Select 2 as data union all Select 3 as data union all Select 4 as data ) T Madhivanan Failing to plan is Planning to fail

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

Check this out:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9193 -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs