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@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant