Select the biggest value

  • I have two variables @a and @B.And I wanted to select largest of the two values.

    For example if @a =2 and @b-2=3

    THen I need to select

    SET @C=@b

    Say if @a=10 and @b-2 =1

    SELECT @C=@a

    IS there any built in function to select the largest of the two values like ISNULL for null value.

    Thanks.

  • You implement If .. Then .. Else type logic in T-SQL using CASE ... WHEN:

    SELECT @C = CASE WHEN @a > @b-2 THEN @a ELSE @b-2 END

     

  • Thanks for your quick response and infact I was wondering if there is any built function to do the same instead of case statement.

    Thanks.

  • nope.

    If like me you are CASE-averse, you can do it with arithmetic, but I don't see any advantage, except the satisfaction of not using CASE, and handling NULLs (but a minor change to the ad-hoc version would do that too, of course)

    create

    function dbo.maxoftwo (@1 int, @2 int)

    returns

    int

    as
    begin
    declare @i int
    select @i = (@1+@2+abs(@1-@2))/2

    return

    @i

    end

    go
     

    select

    dbo.maxoftwo(256789,13728)

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Tim,

    Do you have a "MinOfTwo" function for a fellow "CASE Adverse" compatriot?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Never mind Well, duh!!!

    select @i = (@1+@2-abs(@1-@2))/2

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • There is an inbuilt function for that.

    It's "MAX". It's strange you did not know about that.

    Actually TSQL is a language to deal with databases.

    And databases hold all values in tables.

    Deal with it.

    Don't declare variables, keep values in tables - and you'll get happy life without problems.

    DECLARE @Table TABLE (Val int)

    INSERT INTO @Table (VAL)

    SELECT @a

    UNION

    SELECT @b-2

    SELECT MAX(VAL) from @Table

    Or, without declaring table:

    SELECT MAX(VAL)

    from (

    SELECT @a VAL

    UNION

    SELECT @b-2

    ) T

     

    _____________
    Code for TallyGenerator

  • Yep... could be done that way and, as usual, is very easy and yet creative on your part.  I was thinking something similar but I was really intrigued by the math behind the formula that Tim posted.  Seems so simple...

    Tim's formula is only good for two values unless you cascade the formula.  Your idea is much better than CASE or the MaxOfTwo formula if you have more than two values to contend with.  I'm thinking function, here...

    Thanks, Serqiy... always a pleasure bouncing ideas around with you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You always welcome, Jeff.

    When I've been asked (or I am asking ) for something what is not there I start to search not for a solution but for a reason why it's not there. And in 99% of cases there is a strong reason.

    Almost everytime that reason brings me easy and clean solution, not always that I was asked for, but always nice and effective.

    _____________
    Code for TallyGenerator

  • It's too bad that functions don't work like sprocs do when it comes to defaulting inputs so that we could make this function a little wider without having to insert 'DEFAULT' as a parameter for missing parameters...

    Anyway, I can see lot's of things to do with this and, over millions of records, might actually be faster than CASE.  Still direct formulas without using a function are pretty cool, too.  Some day, I test a bunch of the methods... case, direct formula, union select, etc.

    Here's your idea wrapped in a function for convenience.

     CREATE FUNCTION dbo.fnMaxOf2Dates

            (

            @Date01 DATETIME,

            @Date02 DATETIME

            )

    RETURNS DATETIME

         AS

      BEGIN

            RETURN (SELECT MAX(d.Val)

                      FROM (SELECT @Date01 AS Val

                            UNION ALL

                            SELECT @Date02 AS Val)d )

        END

    SELECT dbo.fnMaxOf2Dates('10/12/2000','10/11/2000')

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, you don't need any special function.

    I'm sure you've got our favourite "split" function.

    Concatenate as many values as you need (starting from no values) into delimited string and select MAX from resultset of that function:

    select MAX(convert(datetime, Val, 120))

    FROM dbo.SplitString('2000-10-12, 2000-10-11, 2000-10-10', ',')

     

    _____________
    Code for TallyGenerator

  • Ok... now I get to accuse you of reading minds...   My only distain for that is if you are comparing two or more columns of dates, you would have to convert and concatenate the columns into a single string... there is some overhead in doing that.  For a second, I considered a correleated sub-query but that almost always too slow.  I'm thinking that a dedicated function using your UNION SELECT with the right number of parameters would offer the greatest performance.

    Or... maybe I need more coffee before I do anything else with this

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply