Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 HAVING Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, June 07, 2010 6:48 AM
 Ten Centuries Group: General Forum Members Last Login: 2 days ago @ 9:59 PM Points: 1,177, Visits: 1,531
 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 07, 2010 7:01 AM
 SSCrazy Group: General Forum Members Last Login: Sunday, November 24, 2013 4:07 PM Points: 2,780, Visits: 2,513
 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 QotDWHERE col2 <> 4`How does adding the `HAVING MAX(col2) > 5`end up with the same result?Thanks,
Post #933494
 Posted Monday, June 07, 2010 7:08 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 28, 2013 1:27 PM Points: 1,219, Visits: 1,047
 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 @QotDwhere col2 <> 4having MAX(col2) > 5
Post #933497
 Posted Monday, June 07, 2010 7:19 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 5,597, Visits: 7,798
 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 4Column '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 R2Hi MG,You didn't post the code you used, but I think that you used`HAVING Col2 > 5`whereas the QotD question uses`HAVING MAX(Col2) > 5` Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #933498
 Posted Monday, June 07, 2010 7:24 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 5,597, Visits: 7,798
 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 QotDWHERE 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #933501
 Posted Monday, June 07, 2010 7:28 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:21 AM Points: 1,722, Visits: 2,064
 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 07, 2010 7:35 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Monday, February 25, 2013 12:52 PM Points: 513, Visits: 426
 I hate to admit, but I simply didn't know you can use HAVING without a Group By.
Post #933511
 Posted Monday, June 07, 2010 7:54 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 08, 2013 12:06 PM Points: 191, Visits: 315
 W/D
Post #933518
 Posted Monday, June 07, 2010 7:58 AM
 Mr 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 07, 2010 8:31 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 1:08 PM Points: 2,218, Visits: 2,410
 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 -------------------"The chemistry must be respected." - Walter White"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

 Permissions