Min and Max

From: smoshiro(at)hotmail(dot)com (Sergio Oshiro)
To: pgsql-sql(at)postgresql(dot)org
Subject: Min and Max
Date: 2002-11-29 18:55:54
Message-ID: 18210f93.0211291055.6831ae6d@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

I tried to use min() and max() with group by but I could not get the
expected results:

-- 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;

Any suggestions?

Thanks In Advance,
Sergio Oshiro

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mike Winter 2002-11-29 22:23:58 Question about slow Select when using 'IN'.
Previous Message Thomas Good 2002-11-29 18:41:47 ALTER TABLE x DROP CONSTRAINT fkey