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

Re: distinct / group by assistance.

From: Gavin 'Beau' Baumanis <gavinb(at)eclinic(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: distinct / group by assistance.
Date: 2008-06-28 17:12:05
Message-ID: 90D82C5F-8325-4909-A5B3-5BE9E55CAF02@eclinic.com.au (view raw or flat)
Thread:
Lists: pgsql-sql
Hi Tom I am a nut.

please find below my correct requirements.

select
    a.foo,
    b.bar,
    c.something
from
	table1 a,
inner join
	table2 b on b.id =a.id
left outer join
	table3 on c.id = a.id

If there a multiple rows of the same id in table2,(one (a) to many (b)  
relationship)
  I get all (multiple) rows - as you would expect - of course.

What I need however, is only one row returned per instance of a.id  
that is returned by the above query - the one with the greatest  
b.primaryKey would be ok.

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.

So my understanding of group by is obviously a little dodgy - and  
obviously not quite what I was expecting.
I tried using a sub query and select distinct a.id..... but that  
didn't exactly help either.

I have deliberately included in my example the fact there is an outer  
join too - I am not sue if that matters or not... but just in case  
thought it prudent to include it here.
On 29/06/2008, at 1:43 AM, Tom Lane wrote:

> "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

Please contact me if you should have any questions.

Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.

E: beau(at)palcare(dot)com(dot)au
T: +61 -3 9381 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au

In response to

pgsql-sql by date

Next:From: Gary StainburnDate: 2008-07-01 10:20:41
Subject: Quick select, slow update - help with performance problems
Previous:From: Tom LaneDate: 2008-06-28 15:43:49
Subject: Re: distinct / group by assistance.

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