Received: from sss.pgh.pa.us ([192.204.191.242])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g07NPeB14593
	for <pgsql-sql@postgresql.org>; Mon, 7 Jan 2002 18:25:40 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g07NPfg15062;
	Mon, 7 Jan 2002 18:25:41 -0500 (EST)
To: Frank Bax <fbax@sympatico.ca>
cc: pgsql-sql@postgresql.org
Subject: Re: simple? join 
In-reply-to: <3.0.6.32.20020107170828.0099e8b0@pop6.sympatico.ca> 
References: <3.0.6.32.20020104121000.00996cb0@pop6.sympatico.ca>
	<3.0.6.32.20020107170828.0099e8b0@pop6.sympatico.ca>
Comments: In-reply-to Frank Bax <fbax@sympatico.ca>
	message dated "Mon, 07 Jan 2002 17:08:28 -0500"
Date: Mon, 07 Jan 2002 18:25:41 -0500
Message-ID: <15059.1010445941@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200201/44
X-Sequence-Number: 6135

Frank Bax <fbax@sympatico.ca> writes:
> But first and last can't be duplicated if emp is defined as unique.  If I
> am also selecting a dozen or so other fields from "employee" table, must I
> also include them all in the GROUP BY clause, even though I know "emp"
> identifies a unique row in this table?

SQL92 says you must.

SQL99, however, seems to understand the concept you are putting forward.
If I understand what they're getting at, the notion of "functional
dependency" that is defined at excruciating length in SQL99 is
essentially that a unique key functionally determines all the other
columns in its table.  And the places where SQL92 says something like
"shall reference a grouping column of T or shall be specified within a
<set function specification>" are changed in SQL99 to read "shall
reference a column that is functionally dependent on G or shall be
specified within a <set function specification>" (G being the set of
grouping columns).

Also, SQL99 considers this an optional feature:

         3) Without Feature T301, "Functional dependencies", if T is
            a grouped table, then in each <value expression>, each
            <column reference> that references a column of T shall
            reference a grouping column or be specified in a <set function
            specification>.

So, when and if we get around to implementing this particular SQL99
feature, what you are suggesting will work.  Right now it doesn't
(and I'll wager that darn few other SQL implementations support this
feature as yet, either).

BTW, SQL99's GROUP BY clause seems to have a whole bunch of baroque
new features besides this one.

			regards, tom lane