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)
24 Comments. Leave new
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.
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
perfect
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.
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)
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
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)
You can find more informations about values clause in this post
try this…
SELECT SUM(CASE WHEN DATA IS NULL THEN 0 END)
FROM (Select NULL AS DATA) t
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+],
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
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
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?
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