From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)potentialtech(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: field must appear in the GROUP BY clause or be used |
Date: | 2004-02-27 16:45:23 |
Message-ID: | Pine.LNX.4.33.0402270940200.14052-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, 27 Feb 2004, Bill Moran wrote:
> Hey all.
>
> I've hit an SQL problem that I'm a bit mystified by. I have two different
> questions regarding this problem: why? and how do I work around it?
>
> The following query:
>
> SELECT GCP.id,
> GCP.Name
> FROM Gov_Capital_Project GCP,
> WHERE TLM.TLI_ID = $2
> group by GCP.id
> ORDER BY gcp.name;
>
> Produces the following error:
>
> ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function
OK, let's look at a test table:
id | data
---------
0 | 'abc'
0 | 'def'
1 | 'ghi'
Now, let's use this query:
select id, data from test_table group by id;
what results should I get back?
I have two possible results for the data column, abc and def. But I only
get one row with a 0 in it, so which one of those do I pick?
If I use an aggregate I can be sure to get the first or last one:
select id, max(data) from test_table group by id;
Also, you may want to look at postgresql's extension, "distinct on":
http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-DISTINCT
It can give you the kind of results you want.
select distinct on (id) id, data from test_table;
But is know to be indeterminate, so you may get different results each
time.
From | Date | Subject | |
---|---|---|---|
Next Message | John Sidney-Woollett | 2004-02-27 16:46:30 | Re: field must appear in the GROUP BY clause or be used |
Previous Message | Bill Moran | 2004-02-27 16:39:58 | Re: field must appear in the GROUP BY clause or be used |