How can I group all children by their parent ?

Le 2014-07-17 à 09:08, Arup Rakshit <aruprakshit@rocketmail.com> a écrit :

How can I group all children by their parent ?

id email parent_id
1 test@test.com nil
2 test1@test.com 1
3 email 1
4 email 2
5 email nil
6 email 3

ORDER BY parent_id, id ?

François Beausoleil

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Le 17/07/2014 15:08, Arup Rakshit a �crit :

How can I group all children by their parent ?

id email parent_id
1 test@test.com nil
2 test1@test.com 1
3 email 1
4 email 2
5 email nil
6 email 3
Regards,
Arup Rakshit

Did you mean
SELECT array_agg(id), array_agg(email), parent_id FROM ... GROUP BY
parent_id

id email parent_id
[1,5] [test@test.com,email] nil [2,3] [test1@test.com,email] 1
[4]: [email] 2
[6]: [email] 3

or
SELECT id, email, parentid FROM ... ORDER BY parent_id

id email parent_id
1 test@test.com nil

5 email nil

2 test1@test.com 1
3 email 1
4 email 2
6 email 3

Regards
Mathieu Pujol

ORDER BY parent_id, id ?

François Beausoleil

parent_id .. But why order_by.. I thought I need to group by

parent child email
 1      2        test1@test.com
                3        email

Arup Rakshit wrote

ORDER BY parent_id, id ?

François Beausoleil

parent_id .. But why order_by.. I thought I need to group by

parent child email
 1      2        

test1@

                3        email

The word "group" as you have used it can mean:

"Generate a single record for each parent with all children combined into
that record."

or

"Physically order the output so that while each child still has its own
record all children of the same parent are listed consecutively"

The first solution requires GROUP BY, the second requires ORDER BY

It would help if you could show an example of what you want the output to
look like.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-can-I-group-all-children-by-their-parent-tp5811846p5811851.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, 17 Jul 2014 21:28:14 +0800
Arup Rakshit <aruprakshit@rocketmail.com> wrote:

ORDER BY parent_id, id ?

parent_id .. But why order_by.. I thought I need to group by

Perhaps you need to tell us what your problem is instead of your
solution. What exactly are you trying to accomplish here? Don't
describe it in database terms. Tell us what the real world situation
is and what result you want to see. For example;

I have a list of people related to each other in a parent/child
relationship to any level (e.g. parents, grandparents, etc.) and I want
to list everyone who is a descendant of anyone in the database.

Try for that level of detail and you will probably get more useful
answers. Also, see http://www.catb.org/~esr/faqs/smart-questions.html
for some useful hints on asking questions.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general