SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers

There are plenty of the things life one can make it simple. I really believe in the same. I was yesterday traveling for community related activity. On airport while returning I met a SQL Enthusiast. He asked me if there is any simple way to find maximum between two numbers in the SQL Server. I asked him back that what he really mean by Simple Way and requested him to demonstrate his code for finding maximum between two numbers.

Here is his code:

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 - @Value2))) AS MaxColumn
GO

I thought his logic was accurate but the same script can be written another way. I quickly wrote following code for him and which worked just fine for him.

Here is my code:

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
GO

He agreed that my code is much simpler but as per him there is some problem with my code which apparently he does not remember at this time. There are cases when his code will give accurate values and my code will not. I think his comment has value but both of us for the moment could not come up with any valid reason.

Do you think any scenario where his code will work and my suggested code will not work?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – Download Whitepaper – SQL Server 2008 R2 Analysis Services Operations Guide
Next Post
SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com

Related Posts

24 Comments. Leave new

  • what if the value is null?

    Reply
    • NULL propagates through the expression and will return NULL as the answer. This is true for any mathematical function in SQL, I believe, so they both should return NULL if any input is NULL.

      Reply
  • Varinder Sandhu
    June 26, 2011 11:58 am

    Case 1

    DECLARE @Value1 DECIMAL(5,2) = Null
    DECLARE @Value2 DECIMAL(5,2) = 8.34
    SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
    GO

    Result = 8.34

    Case 2

    DECLARE @Value1 DECIMAL(5,2) = 9.22
    DECLARE @Value2 DECIMAL(5,2) = Null
    SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
    GO

    Result = Null

    Reply
  • Haresh Ambaliya
    June 26, 2011 1:07 pm

    perfect

    Reply
  • Haresh Ambaliya
    June 26, 2011 1:32 pm

    If you just handle null in condition like

    DECLARE @Value1 DECIMAL(5,2) = 2
    DECLARE @Value2 DECIMAL(5,2) = null
    SELECT CASE WHEN @Value1 > isnull(@Value2,0) THEN @Value1 ELSE @Value2 END AS MaxColumn
    GO

    then there is no impact of null.

    Reply
    • Varinder Sandhu
      June 26, 2011 6:24 pm

      Yes your right Haresh, if we handle the null condition. it seems okay

      but actually i gave these cases based upon the script as given in post.

      Reply
      • nice

      • No, that’s not right..
        If @value will be a negative number, it will not output that one, but will output 0 as a maximum (which is not even a ‘real’ value)

  • Varinder Sandhu
    June 26, 2011 3:10 pm

    Same thing if we check with other code example (without handle null)

    Case 1

    DECLARE @Value1 DECIMAL(5,2) = 9.22
    DECLARE @Value2 DECIMAL(5,2) = Null
    SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 – @Value2))) AS MaxColumn
    GO

    Result = NULL

    Case 2

    DECLARE @Value1 DECIMAL(5,2) = Null
    DECLARE @Value2 DECIMAL(5,2) = 8.34
    SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 – @Value2))) AS MaxColumn
    GO

    Result = NULL

    Reply
  • Jonathan Massey
    June 26, 2011 3:36 pm

    Haresh – that’s only a solution if a) you’re dealing with positive numbers and b) you consider the comparison against null to be valid. I’d argue that any comparison against NULL should return NULL.

    DECLARE @Value1 DECIMAL(5,2) = 2
    DECLARE @Value2 DECIMAL(5,2) = NULL
    SELECT CASE
    WHEN @Value1 > @Value2 THEN @Value1
    WHEN @Value2 > @Value1 THEN @Value2
    ELSE NULL
    END AS MaxColumn

    Will return the larger value if both are NOT NULL and unequal, and will return NULL if either of the values are NULL or are equal. I’d argue that that is the more correct behaviour.

    Reply
  • Hi Pinal,

    Both codes are not working if @Value2 = NULL as both queries are returning NULL instead of 9.22

    DECLARE @Value1 DECIMAL(5,2)
    DECLARE @Value2 DECIMAL(5,2)
    SET @Value1 = 9.22
    SEt @Value2 = NULL
    SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
    SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 – @Value2))) AS MaxColumn
    GO

    But, if @Value1 = NULL then your code is working and his code is not –

    DECLARE @Value1 DECIMAL(5,2)
    DECLARE @Value2 DECIMAL(5,2)
    SET @Value1 = NULL
    SEt @Value2 = 8.34
    SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
    SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 – @Value2))) AS MaxColumn
    GO

    Cheers…

    Shekhar Teke
    Sr DBA
    Talkingtech Limited,
    Hamilton, New Zealand

    Reply
  • Another way… :)
    select Max(val) from (values(@Value1),(@Value2)) as t(val)

    Reply
  • try this…

    SELECT SUM(CASE WHEN DATA IS NULL THEN 0 END)
    FROM (Select NULL AS DATA) t

    Reply
  • Alexandru Gatej
    June 27, 2011 12:47 pm

    If you change the data type to float you will have the well know problems with floating points. Maybe this is the case when the script was not ok

    Reply
  • Here are some alternate methods

    Reply
  • How about ensuring SQL understands the datatype in use:

    SELECT SUM(data)
    FROM (SELECT CAST(NULL as int) AS DATA) t

    Reply
  • DECLARE @Value1 DECIMAL(5,2)
    DECLARE @Value2 DECIMAL(5,2)
    SET @Value1 = NULL
    SET @VALUE2= 8.34
    SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn

    IF @Value1 is null it return 8.34
    so 8.34 is greater than null? when null is not

    Reply
  • Hi I trying to in each case use between two value e.g. >15 and < 25

    SELECT TOP (100) PERCENT [Subject Line],
    AVG(CASE LEN([Subject Line]) WHEN 15 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [15-20],
    AVG(CASE LEN([Subject Line]) WHEN 21 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [21-30],
    AVG(CASE LEN([Subject Line]) WHEN 34 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [31-40],
    AVG(CASE LEN([Subject Line]) WHEN 41 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [41-50],
    AVG(CASE LEN([Subject Line]) WHEN 51 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [51-60],
    AVG(CASE LEN([Subject Line]) WHEN 61 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [61-70],
    AVG(CASE LEN([Subject Line]) WHEN 71 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [71-80],
    AVG(CASE LEN([Subject Line]) WHEN 81 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [81-90],
    AVG(CASE LEN([Subject Line]) WHEN 91 THEN ROUND(ISNULL((A.OpenRate), 0), 0) END) AS [91+],

    Reply
  • How about this query…

    DECLARE @Value1 DECIMAL(5,2) = -9
    DECLARE @Value2 DECIMAL(5,2) = null
    ;With NumberCollection(Numbers)
    AS
    (
    Select @Value1
    Union
    Select @Value2
    )
    Select MAX(Numbers) as Maximum from NumberCollection

    Reply
  • card# bookid Checkoutdate Returndate
    1354 1 2012-09-01 00:00:00 NULL
    1373 2 2012-09-10 00:00:00 NULL
    1348 20 2012-09-03 00:00:00 2012-09-04 00:00:00
    1349 7 2012-09-05 00:00:00 2012-09-06 00:00:00
    1350 8 2012-08-31 00:00:00 2012-09-10 00:00:00
    1351 11 2012-07-25 00:00:00 2012-07-28
    1354 13 2012-07-24 00:00:00 NULL
    1354 17 2012-07-24 00:00:00 NULL
    1354 18 2012-07-24 00:00:00 NULL
    1354 19 2012-07-24 00:00:00 NULL

    i have this table. how can i find ‘Reurndate’ of one or more days later as compare to ‘Checkoutdate’ without using max function how could be possible. please tell me

    Reply
  • A function like C# Math.Max would be awesome

    Reply
  • Allen Boroumand
    June 30, 2014 11:07 pm

    I have SELECT ORCM.Quantity AS Qty, FROM ORCM WHERE ORCM.Quantity = “7”
    How can I check to see if ORCM.Quantity is null or is not null?

    Reply
  • Zach Gelnett (@zachg99)
    September 4, 2015 9:50 pm

    To handle one of the values being null, put the isnull in the else statement.

    DECLARE @Value1 DECIMAL(5,2) = 9.22
    DECLARE @Value2 DECIMAL(5,2) = 8.34
    SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE isnull(@Value2,@Value1) END AS MaxColumn
    GO

    Reply

Leave a Reply

Menu