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 05, 2010 12:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 5,794, Visits: 8,007
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 06, 2010 3:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
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 06, 2010 5:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 5,794, Visits: 8,007
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 06, 2010 11:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:13 AM
Points: 1,865, Visits: 368
thanks Hugo, i really didnt know this. I was assuming that HAVING must have a corresponding GROUP by.
Post #933340
Posted Monday, June 07, 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 02, 2014 9:57 AM
Points: 554, Visits: 863
Bit Tricky & thanks for the Good question :)
Post #933344
Posted Monday, June 07, 2010 12:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
High quality, thats what I like in the mornings - Thanks Hugo!

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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
Good Question, we will be always thinking that having can be used only with the Group By clause.

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
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 07, 2010 4:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 09, 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 07, 2010 6:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:27 AM
Points: 1,757, Visits: 2,118
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