Date: Mon, 2 Dec 2002 13:46:56 -0500
From: Joel Burton <joel@joelburton.com>
To: Sergio Oshiro <smoshiro@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Min and Max
Message-ID: <20021202184656.GC12953@temp.joelburton.com>
References: <18210f93.0211291055.6831ae6d@posting.google.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <18210f93.0211291055.6831ae6d@posting.google.com>
User-Agent: Mutt/1.3.25i

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