Chris Cradock (9/1/2010)
Given that 'having' should be executed after the aggregate has been formed, and code2 isn't in the column list, I don't see why the line is even valid
Why do you think that Col2 should be in the column list? There is no rule that says that HAVING can only use expressions from the column list. Would you also have objected to the code below (that still produces an empty result set?
SELECT COUNT(*), MAX(Col2)
FROM QotD
WHERE Col2 <> 4
HAVING MAX(Col2) < 5;
Chris Cradock (9/1/2010)
Certainly other DBs complain about it that way.So be wary, its not documented for a reason.
Which DBs did you test this on? I only have access to SQL Server and *cough*MS-Access*cough*, and it behaves the same in both versions. I'd love to hear which ones produce a different result, as that would be a violation of the ANSI/ISO standards for those products.
And it is far from "not documented". It is only "not documented well in Books Online". The behaviour of a query with HAVING but no GROUP BY is documented very explicitly in the SQL standards. Here are some relevant quotes from the (agree, very old, but still the basis for many modern RDBMSes) SQL-92 standard:
7.8 <having clause>
(...)
General Rules
1) Let T be the result of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then T consists of a single group and does not have
a grouping column.
(...)
Or, if you prefer a more modern standard, here is a quote from the SQL-2003 standard:
7.10 <having clause>
(...)
Syntax Rules
1) Let HC be the <having clause>. Let TE be the <table expression> that immediately contains HC. If TE
does not immediately contain a <group by clause>, then “GROUP BY ()” is implicit.
(...)
I'll leave it to you to look up the exact definition of GROUP BY () in SQL-2003.