# 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)

## SQL SERVER – Applying Query Hints to Views

• what if the value is null?

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

• 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

• Haresh Ambaliya
June 26, 2011 1:07 pm

perfect

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

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

• 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

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

• 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

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

• try this…

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

• 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

• Here are some alternate methods

• How about ensuring SQL understands the datatype in use:

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

• 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

• 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+],

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

• 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

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

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