SQL Server Performance

Oracle Least/Greatest functions in T-SQL

Discussion in 'General Developer Questions' started by gagiovlasitis, Feb 9, 2006.

  1. gagiovlasitis New Member

    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
  2. Madhivanan Moderator


    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
  3. Adriaan New Member

    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
  4. Adriaan New Member

    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.
  5. gagiovlasitis New Member

    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
  6. Adriaan New Member

    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
  7. gagiovlasitis New Member

    Thanks. That'll do it.
  8. FrankKalis Moderator

  9. mmarovic Active Member

    That was funny contest [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  10. Adriaan New Member

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

Share This Page