Re: distinct / group by assistance.

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