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