Thread: 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

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

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.

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