"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
select a.id, min(a.foo), avg(b.bar), ... from ... group by a.id;
regards, tom lane
In response to
pgsql-sql by date
|Next:||From: Gavin 'Beau' Baumanis||Date: 2008-06-28 17:12:05|
|Subject: Re: distinct / group by assistance. |
|Previous:||From: Gavin 'Beau' Baumanis||Date: 2008-06-28 14:32:40|
|Subject: distinct / group by assistance.|