X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8B6DBD7791
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 18:21:30 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 65712-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 21:21:25 +0000 (GMT)
Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25])
	by svr1.postgresql.org (Postfix) with ESMTP id 2382CD777D
	for <pgsql-sql@postgresql.org>; Tue, 11 Oct 2005 18:21:25 -0300 (ADT)
Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP
	with MS-WebStorage 6.5.6944
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	11 Oct 2005 16:21:27 -0500
Subject: Re: pg, mysql comparison with "group by" clause
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <001001c5cea8$77cabc90$0300a8c0@dell8200>
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1129065687.29961.114.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Tue, 11 Oct 2005 16:21:27 -0500
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.007 required=5 tests=[AWL=0.007]
X-Spam-Level: 
X-Archive-Number: 200510/96
X-Sequence-Number: 22949

On Tue, 2005-10-11 at 16:12, 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?

Yes, you are correct.  The SQL standard is quite clear that in order to
appear in the select list, an entry must either be in the group by or be
operated upon by an aggregate function.  PostgreSQL supports this same
action by way of the non-standard

select distinct on(<fieldlist>), <fieldlist> from ....

Since you don't know for sure which answer you'll get each time, it's
better to KNOW you're doing something that may not be reproduceable than
to accidentally do it when your database SHOULD be throwing an error.

That's just one of many many things MySQL does that makes my head hurt. 
For more, search google for "mysql gotchas"