SQL Server Performance Forum – Threads Archive

# 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 1Select 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

Thanks. That’ll do it.

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

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=’‘ />]

]]>