johnf <jfabiani@xxxxxxxx> writes:
> On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote:
>> Each of columns that you specify in your SELECT clause, must also
>> appear in the GROPU BY clause.
> Is the requirement of select fields matching group by fields a SQL92
> requirement or something to due to Postgres? I ask because with Visual Fox
> Pro I know that I can have several select fields with only one group by
> field.
It is in fact a SQL92 requirement: section 7.9 <query specification> saith
7) If T is a grouped table, then each <column reference> in each
<value expression> that references a column of T shall refer-
ence a grouping column or be specified within a <set function
specification>.
(A "set function" is what PG calls an aggregate function.)
Later versions of the spec relax that a bit: in SQL99, if you GROUP BY
a primary key (or some other cases that are not too interesting in
practice) then there can be only one row per group anyway and so
references to other columns will have well-defined values. We have not
got around to implementing that extension.
I don't know FoxPro, but there are some DBMSes (cough m***l cough) that
simply let you reference ungrouped columns without any check to see
whether what you have written is sensible or not. The results you get
from such a query are pretty unpredictable, or at least implementation-
dependent.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
