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 Monday, June 7, 2010 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 1,382, Visits: 1,753
Hugo Kornelis (6/6/2010)
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, I think the "one row, value 2" answer was for people so used to group by that they instinctively grouped by col1. Then the HAVING would limit it to col1=2, and the WHERE would have eliminated #4, so you'd get a count of 2. I know I ALMOST made that mistake.
Post #933486
Posted Monday, June 7, 2010 7:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
I am not sure I understand the logic behind this one.

This query will result in a single unnamed column with a value of 5
SELECT COUNT(*)
FROM QotD
WHERE col2 <> 4

How does adding the
HAVING MAX(col2) > 5

end up with the same result?

Thanks,
Post #933494
Posted Monday, June 7, 2010 7:08 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 1,490, Visits: 1,060
Putting MAX(col2) in the result set helped me see what was going on. WHERE clause evaluated first giving one row result set. This one row satisfies HAVING clause.

select COUNT(*), MAX(col2)
from @QotD
where col2 <> 4
having MAX(col2) > 5
Post #933497
Posted Monday, June 7, 2010 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
MG-148046 (6/7/2010)
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

Hi MG,

You didn't post the code you used, but I think that you used
HAVING Col2 &gt; 5

whereas the QotD question uses
HAVING MAX(Col2) &gt; 5




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #933498
Posted Monday, June 7, 2010 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
dbowlin (6/7/2010)
I am not sure I understand the logic behind this one.

This query will result in a single unnamed column with a value of 5
SELECT COUNT(*)
FROM QotD
WHERE col2 <> 4

How does adding the
HAVING MAX(col2) > 5

end up with the same result?

Thanks,

Hi dbowlin,

The reply posted by wware might give you a clue.

The first form is quite well known. We all use SELECT COUNT(*) FROM SomeTable, or SELECT MAX(SomeColumn) FROM SomeTable occasionaly, and expect SQL Server to calculate the aggregate over the entire table, returning a single row.

The HAVING builds on that. There still is a single group containing all rows (except those filtered by the WHERE). If that group satisfies the HAVING, you get a result. If it doesn't the group is discarded and since there are no other groups, the result is empty.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #933501
Posted Monday, June 7, 2010 7:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:29 AM
Points: 1,858, Visits: 2,243
Yep - that's exactly what I did and that makes perfect sense now
Thanks


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 #933504
Posted Monday, June 7, 2010 7:35 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: Friday, April 25, 2014 8:31 AM
Points: 513, Visits: 430
I hate to admit, but I simply didn't know you can use HAVING without a Group By.
Post #933511
Posted Monday, June 7, 2010 7:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 7:23 AM
Points: 200, Visits: 323
W/D
Post #933518
Posted Monday, June 7, 2010 7:58 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: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
UMG Developer (6/6/2010)
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.

If that was one of the choices, I would have got it wrong for sure, because I would have bitten on on it hard. Since it wasn't, I had to try if for my self.

Either way, I learned


Converting oxygen into carbon dioxide, since 1955.

Post #933521
Posted Monday, June 7, 2010 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 2,432, Visits: 2,868
Wow, that question was excellent. It really threw me for a loop and taught me something about the HAVING clause that I never knew before.

Thanks,
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 #933539
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse