Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

HAVING without GROUP BY Expand / Collapse
Author
Message
Posted Tuesday, August 31, 2010 9:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:56 AM
Points: 1,093, Visits: 2,617
Again a great question Hugo! Really makes you think (more than) twice about what to answer..... something new learned...again :)



_______________________________________________________________________
For better assistance in answering your questions, click here
Post #978090
Posted Tuesday, August 31, 2010 9:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 2,391, Visits: 2,782
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



-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #978111
Posted Tuesday, August 31, 2010 10:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #978205
Posted Tuesday, August 31, 2010 12:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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...
Post #978272
Posted Tuesday, August 31, 2010 12:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 6,043, Visits: 8,324
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #978303
Posted Tuesday, August 31, 2010 1:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 2,391, Visits: 2,782
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #978306
Posted Tuesday, August 31, 2010 7:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 17,822, Visits: 15,746
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
Post #978442
Posted Wednesday, September 1, 2010 3:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 6:36 AM
Points: 29, Visits: 67
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.
Post #978569
Posted Wednesday, September 1, 2010 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 6,043, Visits: 8,324
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #978631
Posted Wednesday, September 1, 2010 5:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 6:36 AM
Points: 29, Visits: 67
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.
Post #978660
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse