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 37D46D7260
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 18:32:47 -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 94946-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 21:32:42 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id ACCD2D7819
	for <pgsql-sql@postgresql.org>; Tue, 11 Oct 2005 18:32:43 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 99F8D35443; Tue, 11 Oct 2005 14:32:45 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 8112B35430; Tue, 11 Oct 2005 14:32:45 -0700 (PDT)
Date: Tue, 11 Oct 2005 14:32:45 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause
In-Reply-To: <001001c5cea8$77cabc90$0300a8c0@dell8200>
Message-ID: <20051011142658.C54076@megazone.bigpanda.com>
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level: 
X-Archive-Number: 200510/99
X-Sequence-Number: 22952

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).