August 31, 2010 at 7:34 am
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.
Thanks again - I've been learning a lot lately with these questions.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 31, 2010 at 7:43 am
Good question.....I wish I was a little more awake when I read it. Maybe next time. Thanks.
August 31, 2010 at 8:26 am
webrunner (8/31/2010)
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.Thanks again - I've been learning a lot lately with these questions.
- webrunner
But if I added a GROUP BY clause (e.g. GROUP BY Col1), would you expect two rows (with values 3 and 0), or only one row (with value 3)? Why would HAVING behave differently when there is only one group? Or when there are many groups, but none satsifies the condition?
(I do agree that it's coutnerintuitive, though - heck, I picked the wrong answer myself when I answered the question this morning!)
August 31, 2010 at 8:35 am
Very nice question and great explanation. I could see this coming in handy while troubleshooting someone else's code where they tried to limit their result set but did not know the rules on how SQL Server would handle the having clause. At first glance I would have expected 3 rows returned.
August 31, 2010 at 8:47 am
Hugo Kornelis (8/31/2010)
webrunner (8/31/2010)
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.Thanks again - I've been learning a lot lately with these questions.
- webrunner
But if I added a GROUP BY clause (e.g. GROUP BY Col1), would you expect two rows (with values 3 and 0), or only one row (with value 3)? Why would HAVING behave differently when there is only one group? Or when there are many groups, but none satsifies the condition?
(I do agree that it's coutnerintuitive, though - heck, I picked the wrong answer myself when I answered the question this morning!)
Thanks Hugo for the question. I got it right but only because I had a debate with a coworker years ago if it was possible to have a having statement without a group by.
I also find it interesting that you come up with questions so difficult that even you get them wrong on occasion. lol
BTW I love your questions, I only get about 1/2 of the right but they always make me think.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
August 31, 2010 at 9:04 am
August 31, 2010 at 9:19 am
Hugo Kornelis (8/31/2010)
webrunner (8/31/2010)
Thanks for the great question. The answer still seems counterintuitive to me - I expected that selecting COUNT(*) would return at least one row (the count result, whether 0 or more), but I guess it works differently in these rare cases.Thanks again - I've been learning a lot lately with these questions.
- webrunner
But if I added a GROUP BY clause (e.g. GROUP BY Col1), would you expect two rows (with values 3 and 0), or only one row (with value 3)? Why would HAVING behave differently when there is only one group? Or when there are many groups, but none satsifies the condition?
(I do agree that it's coutnerintuitive, though - heck, I picked the wrong answer myself when I answered the question this morning!)
Sorry, I think my first response was ambiguous. Rather than thinking the result could have many rows, I meant I didn't expect that SELECT COUNT(*) without a GROUP BY could return 0 rows - I figured it would return 1 row (populated with a total of 0 if the HAVING condition was not satisfied). I think I answered "One row displaying the value 0" as a result, but I'm not sure if there's a way to go back and check what I answered.
I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 31, 2010 at 10:55 am
This is an excellent question, thank you Hugo. It reminded me what my old chess computer says when in teaching mode:
The knight is a tough one. It moves like an L
We are definitely getting spoiled by the high quality brain teasers and perfect explanations from you. Too bad that the back to reality day will inevitably come when someone will post something like:
Ha-Ha, got you. That seIect word has a spelling error in it because the third letter there is a capital I, not the small l, so the query in question does not return any results...
Oleg
August 31, 2010 at 12:27 pm
Nice question thanks!
Now if I would just pay more attention and read carefully. (I read it as greater than...)
I should have known better because I think the last question was the > version, so this would have been a complete duplicate...
August 31, 2010 at 12:59 pm
webrunner (8/31/2010)
I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?
Yes, it is.
Suppose I had used this query:
SELECT COUNT(*)
FROM #QotD
WHERE Col2 <> 4
GROUP BY Col1
HAVING MAX(Col2) < 1;
I guess a lot more poeple would have correctly predicted the empty result set. We are all used to metally checking off which groups satisfy the HAVING condition if both GROUP BY and HAVING are in a query. We are also used to expecting a single row result set when aggregates are used without GROUP BY. We are not programmed to do any additional checking on a HAVING without GROUP BY, because this is a very seldom-used construction.
That is what I based this question on - those two automatisms for two "normal" constructions, that lead us to incorrect expectations when the ingredients are mixed in an unusual way.
August 31, 2010 at 1:03 pm
Hugo Kornelis (8/31/2010)
webrunner (8/31/2010)
I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?Yes, it is.
Suppose I had used this query:
SELECT COUNT(*)FROM #QotD
WHERE Col2 <> 4
GROUP BY Col1
HAVING MAX(Col2) < 1;
I guess a lot more poeple would have correctly predicted the empty result set. We are all used to metally checking off which groups satisfy the HAVING condition if both GROUP BY and HAVING are in a query. We are also used to expecting a single row result set when aggregates are used without GROUP BY. We are not programmed to do any additional checking on a HAVING without GROUP BY, because this is a very seldom-used construction.
That is what I based this question on - those two automatisms for two "normal" constructions, that lead us to incorrect expectations when the ingredients are mixed in an unusual way.
Great, thanks once more! I am happier to have learned this lesson correctly than I would have been had I guessed correctly and not understood the answer.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 31, 2010 at 7:27 pm
Excellent question and excellent explanation.
Thanks Hugo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 1, 2010 at 3:08 am
Given that 'having' should be executed after the aggregate has been formed, and code2 isn't in the column list, I don't see why the line is even valid - it should complain about a missing 'col2'. Certainly other DBs complain about it that way.
So be wary, its not documented for a reason.
September 1, 2010 at 4:37 am
Chris Cradock (9/1/2010)
Given that 'having' should be executed after the aggregate has been formed, and code2 isn't in the column list, I don't see why the line is even valid
Why do you think that Col2 should be in the column list? There is no rule that says that HAVING can only use expressions from the column list. Would you also have objected to the code below (that still produces an empty result set?
SELECT COUNT(*), MAX(Col2)
FROM QotD
WHERE Col2 <> 4
HAVING MAX(Col2) < 5;
Chris Cradock (9/1/2010)
Certainly other DBs complain about it that way.So be wary, its not documented for a reason.
Which DBs did you test this on? I only have access to SQL Server and *cough*MS-Access*cough*, and it behaves the same in both versions. I'd love to hear which ones produce a different result, as that would be a violation of the ANSI/ISO standards for those products.
And it is far from "not documented". It is only "not documented well in Books Online". The behaviour of a query with HAVING but no GROUP BY is documented very explicitly in the SQL standards. Here are some relevant quotes from the (agree, very old, but still the basis for many modern RDBMSes) SQL-92 standard:
7.8 <having clause>
(...)
General Rules
1) Let T be the result of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then T consists of a single group and does not have
a grouping column.
(...)
Or, if you prefer a more modern standard, here is a quote from the SQL-2003 standard:
7.10 <having clause>
(...)
Syntax Rules
1) Let HC be the <having clause>. Let TE be the <table expression> that immediately contains HC. If TE
does not immediately contain a <group by clause>, then “GROUP BY ()” is implicit.
(...)
I'll leave it to you to look up the exact definition of GROUP BY () in SQL-2003.
September 1, 2010 at 5:22 am
I'm not attempting to say HAVING without GROUP BY is invalid, and yes adding MAX(col2) to the selected columns I would then expect the behaviour you describe (and I would also reason the challenge would have been a "doddle" for everyone as it would then be obvious what HAVING was up to).
Its the expectation that HAVING has access to anything that wasn't explicitly worked out at the record selection stage, and thus have access to the MAX(col2) value. SQL server essentially extends the columns selected to resolve the HAVING MAX(col2). So it performs the statement in your response. But that's not in the SQL standard as far as I'm aware.
I hope that clarifies my statement.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply