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

Re: distinct / group by assistance.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Gavin 'Beau' Baumanis" <gavinb(at)eclinic(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: distinct / group by assistance.
Date: 2008-06-28 15:43:49
Message-ID: 28895.1214667829@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"Gavin 'Beau' Baumanis" <gavinb(at)eclinic(dot)com(dot)au> writes:
> ... If there a multiple rows of the same id in table1, I get all  
> (multiple) rows - as you would expect - of course.

> What I need however, is only one row returned per instance a.id that  
> is returned by the above query.

You need GROUP BY a.id.

> I thought of using group by - but there are no calculated fields...  
> and the real query contains 32 fields, which according to the errors I  
> ran into while trying to get this working, would all need to be  
> included in the group by clause.

No, you wouldn't want to do that, because then you'd be back to multiple
rows per a.id value.

The problem here is that for any one a.id value there could be multiple
values of the other variables (coming from different rows) and so the
query results are not well defined if you just add "GROUP BY a.id".
What you need to do is decide which of those values you want and use
an aggregate function to get it.  So your query might end up looking
like
	select a.id, min(a.foo), avg(b.bar), ... from ... group by a.id;

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Gavin 'Beau' BaumanisDate: 2008-06-28 17:12:05
Subject: Re: distinct / group by assistance.
Previous:From: Gavin 'Beau' BaumanisDate: 2008-06-28 14:32:40
Subject: distinct / group by assistance.

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