Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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.


In response to

pgsql-general by date

Next:From: John Sidney-WoollettDate: 2004-02-27 16:46:30
Subject: Re: field must appear in the GROUP BY clause or be used
Previous:From: Bill MoranDate: 2004-02-27 16:39:58
Subject: Re: field must appear in the GROUP BY clause or be used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group