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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2008-07-01 10:20:41 | Quick select, slow update - help with performance problems |
Previous Message | Tom Lane | 2008-06-28 15:43:49 | Re: distinct / group by assistance. |