June 5, 2010 at 12:20 pm
Comments posted to this topic are about the item HAVING
June 6, 2010 at 3:39 pm
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.
June 6, 2010 at 5:24 pm
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.
June 6, 2010 at 11:49 pm
thanks Hugo, i really didnt know this. I was assuming that HAVING must have a corresponding GROUP by.
June 7, 2010 at 12:20 am
Bit Tricky & thanks for the Good question 🙂
June 7, 2010 at 12:38 am
High quality, thats what I like in the mornings - Thanks Hugo!
Best Regards,
Chris Büttner
June 7, 2010 at 1:02 am
Good Question, we will be always thinking that having can be used only with the Group By clause.
June 7, 2010 at 2:02 am
Nice question. Learned something about HAVING.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 7, 2010 at 4:17 am
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 HensbergenHelp 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
June 7, 2010 at 6:45 am
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.
June 7, 2010 at 6:48 am
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. :w00t:
June 7, 2010 at 7:01 am
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,
June 7, 2010 at 7:08 am
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
June 7, 2010 at 7:19 am
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 > 5
whereas the QotD question uses
HAVING MAX(Col2) > 5
June 7, 2010 at 7:24 am
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.
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply