Traditionally this happens when HR demands "10 years experience with windows server 2007" and you somehow snuck thru (or maybe worked at MS on the dev team, or were in a beta program or...) anyway by definition the only people making it thru the filter are going to have a weird/cool/interesting background or are going to be pathological liars who inevitably will only get 32 on the 40 point test, because they filtered all the real applicants out by applying a bad filter.
I guess the SQL analogy would be something like:
SELECT COUNT(*)
FROM applicant
WHERE applicant.skill > 40
HAVING applicant.skill < 10 AND applicant.honesty < 0
Ah I was trying to imply two sets of filtering and probably have them reversed, where HR is filtering on lack of honesty which only implies lack of skill or at least average skill, whereas the secondary filtration (the having clause) would imply the test for skill.
This is yet another of those things that gets the pgsql folks all wound up about mysql. mysql is generally permissive best effort rather than restrictive follow the spec, so HAVING is allowed to reference stuff not in a GROUP BY or an aggregate (like MAX or COUNT). As you imply, this is not allowed by the SQL standard so philosophically I would Strongly Expect pgsql to error out unlike mysql. I donno what ms-sql does, don't use microsoft stuff. Oracle costs too much, so another I donno.
About 99% of the noise about mysql vs pgsql boils down to this overarching philosophical different of "try yer best" vs "only perfection is permissible". There are minor other differences aside from that, none of which I can remember at this time.
I was mostly trying to make a joke and making the psuedocode kinda sql inspired rather than cut and paste into a window like a stack exchange answer. I could have implemented it "properly" as a nested subquery I suppose. Or to make the point a little more .. obviously, just "select 0;"
That's messed up. "Having" should only be used for filters on the output aggregate functions, and "where" should be used for filters on the input row data. If mysql lets you use "having" when you mean "where", that is unfortunate.
example:
select count(1) cnt, department
from sales
where department_id in (1, 2, 3, 4, 5)
group by department
having count(1) >= 100;
So, it filters out all the input rows to only those department ids, and then it filters out the aggregate output rows to only those with a count() of 100 or more.
-- List employees who have the biggest salary
-- in their departments
select
Name
from
Employees e1
where
exists
(
select
1
from
Employees e2
where
e2.DepartmentID = e1.DepartmentID
having
max(e2.Salary) = e1.Salary
)
Traditionally this happens when HR demands "10 years experience with windows server 2007" and you somehow snuck thru (or maybe worked at MS on the dev team, or were in a beta program or...) anyway by definition the only people making it thru the filter are going to have a weird/cool/interesting background or are going to be pathological liars who inevitably will only get 32 on the 40 point test, because they filtered all the real applicants out by applying a bad filter.
I guess the SQL analogy would be something like:
SELECT COUNT(*)
FROM applicant
WHERE applicant.skill > 40
HAVING applicant.skill < 10 AND applicant.honesty < 0