HAVING

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Hugo Kornelis » HAVING

54 posts, Page 1 of 6

1 2 3 4 5 »»»

HAVING

Rate Topic

Display Mode

Topic Options

Author

Message

Hugo Kornelis

Posted Saturday, June 05, 2010 12:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 5,235, Visits: 7,041

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

UMG Developer

Posted Sunday, June 06, 2010 3:39 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

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

Hugo Kornelis

Posted Sunday, June 06, 2010 5:24 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 5,235, Visits: 7,041

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

ziangij

Posted Sunday, June 06, 2010 11:49 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,528, Visits: 359

thanks Hugo, i really didnt know this. I was assuming that HAVING must have a corresponding GROUP by.

Post #933340

deepak.a

Posted Monday, June 07, 2010 12:20 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554, Visits: 861

Bit Tricky & thanks for the Good question :)

Post #933344

Christian Buettner-167247

Posted Monday, June 07, 2010 12:38 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 2,525, Visits: 3,618

High quality, thats what I like in the mornings - Thanks Hugo!

Best Regards,
Chris Büttner

Post #933347

sharath.chalamgari

Posted Monday, June 07, 2010 1:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679

Good Question, we will be always thinking that having can be used only with the Group By clause.

Post #933350

Koen Verbeeck

Posted Monday, June 07, 2010 2:02 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,372, Visits: 6,470

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

Ronald H

Posted Monday, June 07, 2010 4:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602

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

MG-148046

Posted Monday, June 07, 2010 6:45 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:47 AM
Points: 1,622, Visits: 1,924

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 »

54 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions