Re: Min and Max

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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant