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 (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers

    • 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.

  1. 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

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

  3. 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

  4. 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.

  5. 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

  6. Pingback: SQL SERVER – Puzzle Involving NULL – Resolve – Error – Operand data type void type is invalid for sum operator Journey to SQLAuthority

  7. 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

  8. Pingback: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com Journey to SQLAuthority

  9. 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

  10. 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+],

  11. 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

  12. 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

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

  14. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s