March 25, 2016 at 4:47 am
Nice. Unfortunately, I focused on the output from SIGN (-1, 0, 1) instead of what was actually in the query. :crying:
By-the-way Microsoft's example of SIGN is terrible, it should show values not equal to the values returned by SIGN.
March 25, 2016 at 6:22 am
Good question, but the explanation is wrong on one point:
The MIN() and MAX() functions are aggregates, therefore the NULL is eliminated.
is not true.
The reason the NULL is eliminated is because of the = comparison.
To illustrate this, change the HAVING clause to:
HAVING MIN(SIGN(MyID)) IS NULL
or
HAVING MAX(SIGN(MyID)) IS NULL
This will return the NULL row, showing that MIN and MAX do not eliminate NULLs in their aggregation (They simply do not treat them as greater than or less than any other values.) If NULLs were eliminated, this should return an empty result set.
March 25, 2016 at 7:10 am
March 25, 2016 at 8:33 am
sknox Posted Today @ 1:22:41 PM
Good question, but the explanation is wrong on one point:
The MIN() and MAX() functions are aggregates, therefore the NULL is eliminated.
is not true.
The reason the NULL is eliminated is because of the = comparison.
To illustrate this, change the HAVING clause to:
HAVING MIN(SIGN(MyID)) IS NULL
or
HAVING MAX(SIGN(MyID)) IS NULL
This will return the NULL row, showing that MIN and MAX do not eliminate NULLs in their aggregation (They simply do not treat them as greater than or less than any other values.) If NULLs were eliminated, this should return an empty result set.
Thanks for a very interesting comments. This modified testing code includes in the results also value NULL with Warning: Null value is eliminated by an aggregate or other SET operation.
DECLARE @MyGroups TABLE(MyId INT NULL);
INSERT @MyGroups SELECT 117 AS MyId
UNION ALL SELECT 18
UNION ALL SELECT 120
UNION ALL SELECT NULL
UNION ALL SELECT 120
UNION ALL SELECT -120
UNION ALL SELECT 52
UNION ALL SELECT -118;
SELECT * FROM @MyGroups;
SELECT MyId, MIN(SIGN(MyId)) AS MIN_SIGN_MyId, MAX(SIGN(MyId)) AS MAX_SIGN_MyId
FROM @MyGroups
GROUP BY MyID
HAVING MIN(SIGN(MyId)) = MAX(SIGN(MyId)) OR MIN(SIGN(MyId)) IS NULL;
March 25, 2016 at 9:02 am
sknox (3/25/2016)
Good question, but the explanation is wrong on one point:The MIN() and MAX() functions are aggregates, therefore the NULL is eliminated.
is not true.
The reason the NULL is eliminated is because of the = comparison.
To illustrate this, change the HAVING clause to:
HAVING MIN(SIGN(MyID)) IS NULLor
HAVING MAX(SIGN(MyID)) IS NULLThis will return the NULL row, showing that MIN and MAX do not eliminate NULLs in their aggregation (They simply do not treat them as greater than or less than any other values.) If NULLs were eliminated, this should return an empty result set.
Well spotted, but both the explanation and your version are incomplete.
The MIN and MAX aggregates do eliminate any NULLs before computing the MIN or MAX. That bit the is something the explanation has right and your version has wrong.
Since we are looking at single values (when grouping is on a single column there can't be two values of that column in a group) when teh value is NULL eliminating that value means the aggregates are left to operate on an empty set, so MIN and MAX both deliver NULL. Neither the explanation nor your version mentions that.
So the having clause for that group amounts to HAVING NULL = NULL which doesn't return TRUE (nor of course does it return FALSE) so that group is discarded, which is what your version says and the explanation leaves out.
It's a good question, I reckon.
The proportion of people picking options 2 and 3 (35% to date) is rather surprising, since it suggests there are lot of people who don't understand GROUP BY, since SIGN isn't used in the GROUP BY clause and those options are clearly based on the idea that the grouping is on the result of calling SIGN. And 7% thinking SIGN isn't a valid function is pretty dismal too.
Tom
March 25, 2016 at 10:51 am
Me too, missed the duplicate rows ! Too distracted by other elements in the logic !
Thanks for the question, I did not know sign() function before. Today I learned something new as well!
March 25, 2016 at 11:48 am
I too had to go and brush up on SIGN. Thanks, Steve!
March 28, 2016 at 3:48 am
Nice question and Great comments..
March 28, 2016 at 6:52 am
sknox (3/25/2016)
Good question, but the explanation is wrong on one point:The MIN() and MAX() functions are aggregates, therefore the NULL is eliminated.
is not true.
The reason the NULL is eliminated is because of the = comparison.
To illustrate this, change the HAVING clause to:
HAVING MIN(SIGN(MyID)) IS NULLor
HAVING MAX(SIGN(MyID)) IS NULLThis will return the NULL row, showing that MIN and MAX do not eliminate NULLs in their aggregation (They simply do not treat them as greater than or less than any other values.) If NULLs were eliminated, this should return an empty result set.
Good point. I've been caught by that "= versus is null" before, especially in a case statement.
March 29, 2016 at 5:17 am
Revenant (3/25/2016)
I too had to go and brush up on SIGN. Thanks, Steve!
Me too. I don't use it much. In fact, I don't remember the last time I used it. Maybe it's buried too far down in the toolbox and got forgotten.
April 2, 2016 at 11:29 am
Thanks for this really interesting question :
1) the comments of everybody were useful as I don't remember the last time where I used SIGN ( maybe 2 or 3 years ago )
2) I discovered that I have forgotten how to write an INSERT statement ( I am using it rarely as I am using T-SQL only thru SMO or articles ). I am beginning to reread the BOL as I have a new database to create , to fill and to display.
3) I knew the SIGN function and HAVING as they were explained in one of the last sessions I attended in 2014 . It is pleasant to have your comments to refresh my memory.
So , many thanks for everybody...
April 4, 2016 at 6:46 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply