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 12345»»»

HAVING Expand / Collapse
Author
Message
Posted Saturday, June 5, 2010 12:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
Comments posted to this topic are about the item HAVING


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #933169
Posted Sunday, June 6, 2010 3:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Great question, I had to think about it, thanks!

Though I would have to say I would have made one of the choices "One row displaying the value 1", as that would have made one of the incorrect choices more plausible.
Post #933284
Posted Sunday, June 6, 2010 5:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
Thanks, UMG!

And you are right, that would have been a better distractor. I'm not sure why I included the "one row, value 2" option. It's a while back I made this question; if I recall correctly, the HAVING clause first tested for >= 5, and I changed this later but apparently forgot to change the distractor.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #933296
Posted Sunday, June 6, 2010 11:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,977, Visits: 369
thanks Hugo, i really didnt know this. I was assuming that HAVING must have a corresponding GROUP by.
Post #933340
Posted Monday, June 7, 2010 12:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
Bit Tricky & thanks for the Good question :)
Post #933344
Posted Monday, June 7, 2010 12:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
High quality, thats what I like in the mornings - Thanks Hugo!

Best Regards,
Chris Büttner
Post #933347
Posted Monday, June 7, 2010 1:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:49 AM
Points: 1,179, Visits: 783
Good Question, we will be always thinking that having can be used only with the Group By clause.

Post #933350
Posted Monday, June 7, 2010 2:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
Nice question. Learned something about HAVING.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #933359
Posted Monday, June 7, 2010 4:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
Nice question! I got it wrong, because I misinterpreted the HAVING clause; I read it as WHERE Col >= 5, and that leads to a value of 2. I knew the behaviour of HAVING though, but I wanted to have it play a role in this query. But alas, it's only to confuse the reader...

So there can always be logic in an incorrect answer, as long as you read the question wrong :)


Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
Post #933405
Posted Monday, June 7, 2010 6:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:10 PM
Points: 1,797, Visits: 2,171
I thought it would be an empty result but wasn't positive decided to "test" and created the table. I got this message on executing the query:

Msg 8121, Level 16, State 1, Line 4
Column 'QotD.col2' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

This is from BOL:

"The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. However, HAVING can be specified without GROUP BY. The HAVING clause specifies additional filters that are applied after the WHERE clause filters. These filters can be applied to an aggregate function used in the select list."

I don't know if the version of SQL makes a difference, but I am using SQL Server 2008 R2


MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #933483
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse