From: Greg Stark <gsstark@mit.edu>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause
Date: 12 Oct 2005 17:54:33 -0400
Message-ID: <87br1u1jvq.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20051011142658.C54076@megazone.bigpanda.com>
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
<20051011142658.C54076@megazone.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, sszabo@megazone.bigpanda.com, rschumeyer@ieee.org
Subject: Re: pg, mysql comparison with "group by" clause
In-Reply-To: <87br1u1jvq.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox