August 30, 2010 at 8:37 pm
Comments posted to this topic are about the item HAVING without GROUP BY
August 30, 2010 at 9:25 pm
Nice one.
August 30, 2010 at 9:43 pm
thanks for this wonderful question... 🙂
August 30, 2010 at 10:32 pm
Nice. Helps if I remember that 6 > 5 :blush:
August 30, 2010 at 11:59 pm
Thanks Hugo - this is an awesome question! Tests fundamentals and has appropriate choices as well... Pls keep posting such gems!
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
August 31, 2010 at 1:13 am
Toby Harman (8/30/2010)
Nice. Helps if I remember that 6 > 5 :blush:
:laugh: I feel your pain. I managed to miss the point as well - on my own question! :w00t:
Thanks everyone for the nice words!
For those who want to see for themselves, or try playing around with the issue, here is some repro code:
CREATE TABLE #QotD (Col1 int, Col2 int);
go
INSERT INTO #QotD (Col1, Col2)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 6;
go
SELECT COUNT(*)
FROM #QotD
WHERE Col2 <> 4
HAVING MAX(Col2) < 5;
go
DROP TABLE #QotD;
go
August 31, 2010 at 2:15 am
Very good question.. I missed my points but learnt something new today.. 😀
August 31, 2010 at 3:24 am
Another good one. I have been losing points lately but I don't miss them.
{Sorry, I meant I was losing, not getting, points lately but its alright. Now I hope to remember if I get an SQL gotcha in work for possible QOTD. }
August 31, 2010 at 3:27 am
I was expecting an answer of 3 or 5, but I wasn't sure which, so I created a temporary table to check--was somewhat surprised to see no rows at all returned, but now Hugo has explained the answer I see the logic.
August 31, 2010 at 5:57 am
Great question. I was torn between the correct answer and the single record returning 0... I don't mind having gotten it wrong. 😀
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 31, 2010 at 6:14 am
Thank you for the question and the detailed explaination
August 31, 2010 at 6:33 am
I don't mind being wrong (heck, if I were right all the time...) but it does mystify me a bit that the HAVING clause empties the result set. I selected 0 because I considered the Having clause to act with the WHERE clause.
So for curiosity's sake, I changed HAVING to AND, ran the command and got the error:
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
That actually helps me to understand it better (I don't claim to be a super pro like many of you here); if HAVING works as an aggregator, the condition says there is nothing to aggregate, thus the empty result set.
Of course, I could be completely wrong with my analysis, too, but for the moment it makes sense to me.
August 31, 2010 at 7:01 am
brazumich (8/31/2010)
I don't mind being wrong (heck, if I were right all the time...) but it does mystify me a bit that the HAVING clause empties the result set. I selected 0 because I considered the Having clause to act with the WHERE clause.So for curiosity's sake, I changed HAVING to AND, ran the command and got the error:
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
That actually helps me to understand it better (I don't claim to be a super pro like many of you here); if HAVING works as an aggregator, the condition says there is nothing to aggregate, thus the empty result set.
Of course, I could be completely wrong with my analysis, too, but for the moment it makes sense to me.
Hi brazumich,
You are not completely wrong, but not completely right either.
Every query that includes aggregate functions and/or a HAVING clause is considered an aggregated query (and every query that includes neither is considered a non-aggregateed query). In each aggregated query, groups are formed (logically, this happens after evaluation the FROM and WHERE clauses; the actual execution plan might differ though).
How these groups are formed is determined by the GROUP BY clause. If there is no such clause, a single group is formed. That is why commonly used queries such as "SELECT COUNT(*), MIN(SomeColumn) FROM MyTable;" return a single row with the rowcount and the maximum SomeCOlumn value for the entire table.
Omitting the GROUP BY before a HAVING clause is far less common than omitting the GROUP BY when an aggregate function is used, but it does have the same effect. So in this question, after flitering out the one row that does not match the WHERE clause, the remaining 5 rows form a single group. That group is then passed through the HAVING clause - and because the MAX(Col2) is 6, the entire group fails the HAVING clause. Hence the empty result set.
If you change the HAVING clause to HAVING MAX(Col2) > 5, you'll get a single row (because the HAVING without GROUP BY forces SQL Server to form one group; and because this time the group does satsify the condition in the HAVING clause). The content of that row will be the number 5, because there are 5 rows in the group (one has fallen victim to the WHERE clause).
August 31, 2010 at 7:11 am
Thank you much for the explanation and for taking the time to write it up.
Bob
August 31, 2010 at 7:14 am
Hugo,
Great question!
It's too bad that this latest post wasn't part of the explanation for the question though - it's beautiful.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply