Postgres Pro
Downloads
Home   >   mailing lists

Thread: Min and Max

Min and Max

From
smoshiro@hotmail.com (Sergio Oshiro)
Date:
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

Re: Min and Max

From
Dennis Björklund
Date:
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,
childrenwherechildren.id_father = r.id_father  and children.min = r.min;
 

-- 
/Dennis

Re: Min and Max

From
Joel Burton
Date:
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

Re: Min and Max

From
Jean-Luc Lachance
Date:
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