On Fri, Nov 29, 2002 at 10:55:54AM -0800, Sergio Oshiro wrote:
> Hello, everybody!
>
> I've trouble to make a "simple"(?) query...
>
> The following table is an example:
>
> table: children
> id_father | child_name | child_age
> ----------+------------+------------
> 1 | John | 2
> 1 | Joe | 3
> 1 | Mary | 4
> 1 | Cristine | 4
> 2 | Paul | 1
> 2 | Stephany | 2
> 2 | Raul | 5
>
> How can I get the rows of the children name and its "father" such that
> they have the min child_ages? I expect the following rows as result:
>
> id_father | child_name | child_age
> ----------+------------+------------
> 1 | John | 2
> 2 | Paul | 1
>
> The same for the max child_ages...
>
> id_father | child_name | child_age
> ----------+------------+------------
> 1 | Mary | 4
> 1 | Cristine | 4
> 2 | Raul | 5
select distinct on (id_father) * from children order by id_father,
child_age;
will give your results
select distinct on (id_father) * from children order by id_father,
child_age desc;
will give the oldest children, but it doesn't list both mary and
christine -- it arbitrarily lists mary (you could add child_name to sort
order so it wouldn't be abitrary, but it still won't list both).
this is a weird use of distinct on, though, and perhaps cheating. a
canonical, if slower solution (and one that fixes the tie for oldest
child) is:
select id_father, child_name, child_age
from children c1
where not exists (select * from children c2 where c1.id_father=c2.id_father and
c2.child_age> c1.child_age);
swap the '>' to '<' for youngest.
- J.
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
