Re: field must appear in the GROUP BY clause or be used

Hey guys

I have dealt with this before.

And there is a simple solution: If the value really is unique, just wrap it
in a max(). Since it's unique, it has *ZERO* effect on your output, but it
then complies to PostgreSQL's GROUP BY implementation, and hence will run...

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com
Fax: (416) 441-9085

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: Friday, February 27, 2004 1:09 PM
> To: Mike Mascari
> Cc: Bill Moran; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] field must appear in the GROUP BY clause or be
> used
>
>
> Mike Mascari <mascarm(at)mascari(dot)com> writes:
> > Bill Moran wrote:
> >> SELECT GCP.id,
> >> GCP.Name
> >> FROM Gov_Capital_Project GCP,
> >> WHERE TLM.TLI_ID = $2
> >> group by GCP.id
> >> ORDER BY gcp.name;
> >>
> >> ERROR: column "gcp.name" must appear in the GROUP BY
> clause or be used
> >> in an aggregate function
> >>
> >> This isn't my query, I'm translating a system prototyped
> in MSSQL to
> >> Postgres. This query _does_ work in MSSQL. Does that constitute a
> >> bug in MSSQL, or a shortcomming of Postgres, or just a
> difference of
> >> interpretation?
>
> > If MSSQL picks an arbitrary value for the non-group by
> attribute, it
> > is violating spec.
>
> They might be operating per spec. If "id" is a primary or unique key
> for the table, then SQL99 (but not SQL92) says that it's sufficient to
> group by the id column; the database is supposed to realize that the
> other columns can't have more than one value per group, and
> allow direct
> references to them. Or at least that's my interpretation of the pages
> and pages in SQL99 about functional dependency. It seems
> like a pretty
> useless frammish ... if you know that id is unique, why are you
> bothering with GROUP BY at all?
>
> Anyway, Postgres currently implements the SQL92 definition, which is
> that you can't refer to an ungrouped column except within an aggregate
> function call. So you need to call out all the columns to be
> referenced
> in GROUP BY.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>