> On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote:
> > Comments? Can anyone confirm whether DB2 or other databases allow
> > ungrouped column references with HAVING?
Mysql treats ungrouped columns as an assertion that those columns will all be
equal for the group and it can pick an arbitrary one. Essentially it does an
implicit "first(x) AS x". The expected use case is for things like:
select count(*),a.* from a,bwhere a.pk = b.a_fkgroup by a.pk
I've noticed quite frequently scenarios where this idiom would be very handy.
I usually either end up rewriting the query to have nested subqueries so I can
push the grouping into the subquery. This doesn't always work though and
sometimes I end up listing several, sometimes dozens, of columns like
"first(x) AS x" or else end up
--
greg