brazumich (8/31/2010)
I don't mind being wrong (heck, if I were right all the time...) but it does mystify me a bit that the HAVING clause empties the result set. I selected 0 because I considered the Having clause to act with the WHERE clause.So for curiosity's sake, I changed HAVING to AND, ran the command and got the error:
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
That actually helps me to understand it better (I don't claim to be a super pro like many of you here); if HAVING works as an aggregator, the condition says there is nothing to aggregate, thus the empty result set.
Of course, I could be completely wrong with my analysis, too, but for the moment it makes sense to me.
Hi brazumich,
You are not completely wrong, but not completely right either.
Every query that includes aggregate functions and/or a HAVING clause is considered an aggregated query (and every query that includes neither is considered a non-aggregateed query). In each aggregated query, groups are formed (logically, this happens after evaluation the FROM and WHERE clauses; the actual execution plan might differ though).
How these groups are formed is determined by the GROUP BY clause. If there is no such clause, a single group is formed. That is why commonly used queries such as "SELECT COUNT(*), MIN(SomeColumn) FROM MyTable;" return a single row with the rowcount and the maximum SomeCOlumn value for the entire table.
Omitting the GROUP BY before a HAVING clause is far less common than omitting the GROUP BY when an aggregate function is used, but it does have the same effect. So in this question, after flitering out the one row that does not match the WHERE clause, the remaining 5 rows form a single group. That group is then passed through the HAVING clause - and because the MAX(Col2) is 6, the entire group fails the HAVING clause. Hence the empty result set.
If you change the HAVING clause to HAVING MAX(Col2) > 5, you'll get a single row (because the HAVING without GROUP BY forces SQL Server to form one group; and because this time the group does satsify the condition in the HAVING clause). The content of that row will be the number 5, because there are 5 rows in the group (one has fallen victim to the WHERE clause).