From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause
Date: Tue, 11 Oct 2005 16:21:27 -0500
Message-ID: <1129065687.29961.114.camel@state.g2switchworks.com> (raw)
In-Reply-To: <001001c5cea8$77cabc90$0300a8c0@dell8200>
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
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"
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: smarlowe@g2switchworks.com, rschumeyer@ieee.org
Subject: Re: pg, mysql comparison with "group by" clause
In-Reply-To: <1129065687.29961.114.camel@state.g2switchworks.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