#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    6
    Rep Power
    0

    Select the smaller of two numbers


    Hi,

    I wondered if there are mathematical MIN and MAX functions which select the smaller or larger of two numbers in Microsoft SQL? I found nothing in the manual.
    For example: min(3,5)==3
    max(1,2)==2
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391

    Smile


    min:

    Code:
    case when a < b then a else b end
    max:

    Code:
    case when a > b then a else b end
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    1
    Rep Power
    0
    without using specific SQL commands, as the min() and max() functions relate to columns rather than numbers. You could go with the "simple math trick" solution.

    Wherever you want to find the smaller or larger, just insert one of these functions in place of any 'command'

    Smaller of two numbers

    (0.5*(A+B) - abs(A-B))

    Larger of two numbers

    (0.5*(A+B) + abs (A-B))

    Comments on this post

    • pabloj agrees
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    6
    Rep Power
    0

    Thumbs up


    Thank you!
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    the math is actually pretty clever

    now axe him to do it with 3 numbers

    see if he comes up with anything as simple as the Standard SQL functions LEAST() and GREATEST()

    oh, wait, SQL Server doesn't support them

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391

    Smile


    Standard SQL functions LEAST() and GREATEST()
    Nope, they are not standard.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    thanks, swampboogie, i thought they were, but you're right, they aren't
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0

    Formula given for smaller of two numbers was incorrect


    Here are the correct formulae...

    smaller: 0.5*((A+B)-abs(A-B))
    larger: 0.5*((A+B)+abs(A-B))

    The leftmost parenthesis was not in the correct place. In order for the formula to work correctly, .5 must be multiplied upon the entire expression. The reason each formula works is because the first expression (after being divided by 2 aka multiplied by .5) yields the average or midpoint between the two values. The second expression adds back half the difference between the two numbers to obtain the larger value or subtracts half the difference to obtain the smaller value.

    This was a very clever formula once the misplaced parenthesis was corrected. I used the formula for vector analysis of electronic circuits where I needed to find the cosine of an angle or the cosine of 180 minus the angle, whichever angle was the smaller. So I greatly appreciate the person who posted this formula, even though it had a minor problem!

IMN logo majestic logo threadwatch logo seochat tools logo