17 ms·

Don't you need a group by clause in order to have a having clause?

by versk 13y ago

VLM 13y ago
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;"
- socialist_coder 13y ago
  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. This is how Oracle and MS-SQL server work.
genwin 13y ago
In SQL Server a HAVING without GROUP BY is a way to filter out duplicates.
- jmelloy 13y ago
  It seems to error out. select name, count(*) from queries having count(*) > 1 Column 'queries.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  - genwin 13y ago
    Like this. There is more than one way to do it: -- 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 )

Don't you need a group by clause in order to have a having clause?

by versk 13y ago

Don't you need a group by clause in order to have a having clause?

VLM 13y ago

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;"

socialist_coder 13y ago
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. This is how Oracle and MS-SQL server work.

genwin 13y ago

In SQL Server a HAVING without GROUP BY is a way to filter out duplicates.

jmelloy 13y ago
It seems to error out. select name, count(*) from queries having count(*) > 1 Column 'queries.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
- genwin 13y ago
  Like this. There is more than one way to do it: -- 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 )