Re: looking for some suggestions

Hi Obe,

You suggestion worked perfectly!

I modified my test data so that "Kidg Two" was put before "Kidf Two" who
is older. So my previous query listed the younger kid first. However,
the following query correctly ordered kids by age.

SELECT childparent.parent, sum(childparent.child || ' ') AS "Children"
FROM
(
SELECT p.fname As parent, c.fname As child
FROM persons p
LEFT JOIN dependents d ON p.person_id = d.parent_id
LEFT JOIN persons c ON d.child_id = c.person_id
ORDER BY p.fname, c.birth_date
) AS childparent
GROUP BY childparent.parent ;

parent | Children
------------+--------------------
Momi Three |
Dadj Four |
Kidc One |
Dadh Three |
Mome Two | Kidf Two Kidg Two
Momb One | Kidc One
Dadd Two | Kidf Two Kidg Two
Dada One | Kidc One
Kidg Two |
Kidf Two |

Thanks for your help.

- Chansup

Obe, Regina DND\MIS wrote:
> Haven't tried this, but I think the aggregate sums in the sequence that
> the records come so if you want ordered by age then you would force your
> list to be ordered by birthdate before it is summed. This is a little
> tricky though since the order by date would screw up your group by so to
> get around that, you would do a subselect that orders your list and then
> sum that. Hopefully this will work.
>
> So something like
>
> SELECT childparent.parent, sum(childparent.child || ' ') AS "Children"
> (SELECT p.fname As parent, c.fname As child
> FROM persons p
> LEFT JOIN dependents d ON p.person_id = d.parent_id
> LEFT JOIN persons c ON d.child_id = c.person_id
> ORDER BY p.fname, c.birth_date) as childparent
> GROUP BY childparent.parent ;
>
> ------------------------------------------------------------------------
> *From:* Chansup Byun [mailto:Chansup(dot)Byun(at)Sun(dot)COM]
> *Sent:* Mon 5/8/2006 9:20 AM
> *To:* Obe, Regina DND\MIS
> *Cc:* pgsql-novice(at)postgresql(dot)org
> *Subject:* Re: [NOVICE] looking for some suggestions
>
> Obe, Regina DND\MIS wrote:
> > I think the most efficient way to do this is with an aggregate function.
> >
> > You can define an aggregate for a varchar and text if one doesn't exist
> > in your database - like so
> >
> > CREATE AGGREGATE sum(
> > BASETYPE=text,
> > SFUNC=textcat,
> > STYPE=text
> > );
> > ALTER AGGREGATE sum(text) OWNER TO postgres;
> >
> > Then you can rewrite your sql statement like so
> >
> > SELECT p.fname As parent, sum(c.fname || ' ') AS "Children"
> > FROM persons p LEFT JOIN dependents d ON p.person_id = d.parent_id LEFT
> > JOIN c.persons c ON d.child_id = c.person_id
> > GROUP BY p.fname
> >
>
> I followed your suggestion and was able to aggregate child names.
>
> boc=# SELECT p.fname As parent, sum(c.fname || ' ') AS "Children"
> boc-# FROM persons p
> boc-# LEFT JOIN dependents d ON p.person_id = d.parent_id
> boc-# LEFT JOIN persons c ON d.child_id = c.person_id
> boc-# GROUP BY p.fname;
> parent | Children
> ------------+--------------------
> Momi Three |
> Dadj Four |
> Kidc One |
> Dadh Three |
> Mome Two | Kidf Two Kidg Two
> Momb One | Kidc One
> Dadd Two | Kidf Two Kidg Two
> Dada One | Kidc One
> Kidg Two |
> Kidf Two |
>
>
> Now my next question is how to make sure kids are ordered by ages?
>
> Suppose that I extend the persons table and add birth_date filed, how
> can I order child names by age? Is there a way to put such a check in to
> the aggregate sum(text) function?
>
> create table persons
> (
> person_id serial ,
> fname varchar(32) ,
> birth_date date ,
> CONSTRAINT person_pk PRIMARY KEY(person_id)
> );
>
> Thanks,
>
> - Chansup
>
> >
> >
> >
> > -----Original Message-----
> > From: pgsql-novice-owner(at)postgresql(dot)org
> > [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Chansup Byun
> > Sent: Friday, May 05, 2006 3:22 PM
> > To: pgsql-novice(at)postgresql(dot)org
> > Cc: Chansup(dot)Byun(at)Sun(dot)COM
> > Subject: [NOVICE] looking for some suggestions
> >
> >
> > Hi,
> >
> > I am looking for some suggestions on my test example.
> >
> > I have two tables: one for persons and the other for dependents table,
> > which is shown below. I would like to concatenate all children of the
> > same parents in a single string such as:
> >
> > Children
> > -----------------
> > Kidc One
> > Kidf Two, Kidg Two
> >
> > Can anyone help me how to do that from the following tables?
> >
> > I have installed PostgreSQL 8.1.3.
> >
> > The tables and their records are given below.
> >
> > create table persons
> > (
> > person_id serial ,
> > fname varchar(32) ,
> > CONSTRAINT person_pk PRIMARY KEY(person_id)
> > );
> >
> > create table dependents
> > (
> > parent_id integer not null,
> > child_id integer not null,
> > CONSTRAINT dependents_pk PRIMARY KEY(parent_id, child_id)
> > );
> >
> >
> > insert into persons(fname) values('Dada One');
> > insert into persons(fname) values('Momb One');
> > insert into persons(fname) values('Kidc One');
> >
> > insert into persons(fname) values('Dadd Two');
> > insert into persons(fname) values('Mome Two');
> > insert into persons(fname) values('Kidf Two');
> > insert into persons(fname) values('Kidg Two');
> >
> > insert into persons(fname) values('Dadh Three');
> > insert into persons(fname) values('Momi Three');
> >
> > insert into persons(fname) values('Dadj Four');
> >
> > insert into dependents(parent_id, child_id) values('1', '3'); insert
> > into dependents(parent_id, child_id) values('2', '3'); insert into
> > dependents(parent_id, child_id) values('4', '6'); insert into
> > dependents(parent_id, child_id) values('5', '6'); insert into
> > dependents(parent_id, child_id) values('4', '7'); insert into
> > dependents(parent_id, child_id) values('5', '7');
> >
> > The following attempt can list all the children but I'm not sure how to
> > group them into a single string based on their parents.
> >
> > SELECT DISTINCT c.fname AS "Children"
> > FROM persons p, persons c, dependents d
> > WHERE d.parent_id = p.person_id AND
> > d.child_id = c.person_id
> > ;
> >
> > Children
> > ----------
> > Kidc One
> > Kidf Two
> > Kidg Two
> >
> >
> > Thanks,
> >
> > - Chansup
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
> > -----------------------------------------
> > The substance of this message, including any attachments, may be
> > confidential, legally
> > privileged and/or exempt from disclosure pursuant to Massachusetts
> > law. It is intended
> > solely for the addressee. If you received this in error, please
> > contact the sender and
> > delete the material from any computer.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>