pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMin and Max
4+ messages / 4 participants
[nested] [flat]
* Min and Max
@ 2002-11-29 18:55 Sergio Oshiro <smoshiro@hotmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: smoshiro@hotmail.com @ 2002-11-29 18:55 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Min and Max
@ 2002-12-02 18:44 Dennis Björklund <db@zigo.dhs.org>
parent: Sergio Oshiro <smoshiro@hotmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Dennis Björklund @ 2002-12-02 18:44 UTC (permalink / raw)
To: Sergio Oshiro <smoshiro@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Min and Max
@ 2002-12-02 18:46 Joel Burton <joel@joelburton.com>
parent: Sergio Oshiro <smoshiro@hotmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Joel Burton @ 2002-12-02 18:46 UTC (permalink / raw)
To: Sergio Oshiro <smoshiro@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Min and Max
@ 2002-12-02 19:54 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Dennis Björklund <db@zigo.dhs.org>
0 siblings, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-12-02 19:54 UTC (permalink / raw)
To: Dennis Björklund <db@zigo.dhs.org>; +Cc: Sergio Oshiro <smoshiro@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-12-02 19:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-29 18:55 Min and Max Sergio Oshiro <smoshiro@hotmail.com>
2002-12-02 18:44 ` Dennis Björklund <db@zigo.dhs.org>
2002-12-02 19:54 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-12-02 18:46 ` Joel Burton <joel@joelburton.com>
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