To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Rick Schumeyer <rschumeyer@ieee.org>, pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
	<20051011142658.C54076@megazone.bigpanda.com>
In-Reply-To: <20051011142658.C54076@megazone.bigpanda.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 12 Oct 2005 17:54:33 -0400
Message-ID: <87br1u1jvq.fsf@stark.xeocode.com>
Lines: 34
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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