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
orbe 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