From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Dennis Björklund <db@zigo.dhs.org>
Cc: Sergio Oshiro <smoshiro@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Min and Max
Date: Mon, 02 Dec 2002 14:54:10 -0500
Message-ID: <3DEBBA62.CFFE0F34@nsd.ca> (raw)
References: <Pine.LNX.4.44.0212021939410.9578-100000@zigo.dhs.org>
If you do not mind non standard, how about:
SELECT DISTINCT ON(id_father) * FROM children ORDER BY id_father,
child_age;
Dennis Björklund wrote:
>
> On 29 Nov 2002, Sergio Oshiro wrote:
>
> > How can I get the rows of the children name and its "father" such that
> > they have the min child_ages?
> >
> > -- the following does not return the child_name...
> > select id_father, min(child_age) from children group by id_father;
> > select id_father, max(child_age) from children group by id_father;
>
> You could join one of the above with the table itself and get the result.
> Something like
>
> select *
> from ( select id_father, min(child_age)
> from children
> group by id_father) as r,
> children
> where children.id_father = r.id_father
> and children.min = r.min;
>
> --
> /Dennis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
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: jllachan@nsd.ca, db@zigo.dhs.org, smoshiro@hotmail.com
Subject: Re: Min and Max
In-Reply-To: <3DEBBA62.CFFE0F34@nsd.ca>
* 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