From: Joel Burton <joel@joelburton.com>
To: Sergio Oshiro <smoshiro@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Min and Max
Date: Mon, 2 Dec 2002 13:46:56 -0500
Message-ID: <20021202184656.GC12953@temp.joelburton.com> (raw)
In-Reply-To: <18210f93.0211291055.6831ae6d@posting.google.com>
References: <18210f93.0211291055.6831ae6d@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joel@joelburton.com, smoshiro@hotmail.com
Subject: Re: Min and Max
In-Reply-To: <20021202184656.GC12953@temp.joelburton.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox