From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: 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: Wed, 12 Oct 2005 21:50:46 -0400
Message-ID: <16344.1129168246@sss.pgh.pa.us> (raw)
In-Reply-To: <87zmpez0au.fsf@stark.xeocode.com>
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
<20051011142658.C54076@megazone.bigpanda.com>
<87br1u1jvq.fsf@stark.xeocode.com>
<1129155875.29961.168.camel@state.g2switchworks.com>
<87zmpez0au.fsf@stark.xeocode.com>
Greg Stark <gsstark@mit.edu> writes:
> I think most MySQL users don't stumble on it, they learn it as the way
> to handle the common use case when you join a master table against a
> detail table and then want to aggregate all the detail records. In
> standard SQL you have to write GROUP BY ... and list every single
> column you need from the master table. Forcing the database to do a
> lot of redundant comparisons and sort on uselessly long keys where in
> fact you only really need it to sort and group by the primary key.
Actually, if you're grouping by a table's primary key, the SQL99 spec
says you shouldn't have to explicitly list the other columns from that
table --- they are guaranteed to have unique values per group anyway.
This is a single case in the "functional dependency" stuff. That
verbiage is incredibly dense and I don't think we want to tackle all of
it any time soon, but the primary-key case probably wouldn't be very
hard to implement. We really ought to have this in TODO ... I'm sure
it's been discussed before.
regards, tom lane
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: tgl@sss.pgh.pa.us, gsstark@mit.edu, smarlowe@g2switchworks.com, sszabo@megazone.bigpanda.com, rschumeyer@ieee.org
Subject: Re: pg, mysql comparison with "group by" clause
In-Reply-To: <16344.1129168246@sss.pgh.pa.us>
* 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