Stephan Szabo <sszabo@megazone.bigpanda.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