> 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,b where a.pk = b.a_fk group 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
