Re: pg, mysql comparison with "group by" clause

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:

> On Tue, 11 Oct 2005, Rick Schumeyer wrote:
>
> > I'm not sure what I was thinking, but I tried the following query in pg:
> >
> > SELECT * FROM t GROUP BY state;
> >
> > pg returns an error.
> >
> > Mysql, OTOH, returns the first row for each state. (The first row with
> > "AK", the first row with "PA", etc.)
> >
> > I'm no SQL expert, but it seems to me that the pg behavior is correct, and
> > the mysql result is just weird. Am I correct?
>
> In your case, it sounds like the mysql result is wrong. I believe SQL99
> would allow it if the other columns were functionally dependant upon state
> (as there'd by definition only be one value for the other columns per
> group).

I believe this is a documented feature.

MySQL treats "select a,b from t group by a" equivalently to Postgres's
"select distinct on (a) a,b from t"

I suppose "equivalent" isn't quite true. It's more general since it allows
aggregate functions as well. The equivalently general Postgres syntax is to
have a first() aggregate function and do "select a,first(b) from t group by a".

I'm sure it's very convenient.

--
greg