SQL-POSTGRESQL : sorting table according to a certain criteria

Question

So I have this table

person_id    child      mom 

1            john       marry
2            mark       sophia
3            emma       null
4            alfonso    marry
5            sophia     null
6            marry      isabella
7            michael    emma
8            isabella   null

I want to sort this table in a way that every mom has her children under her. For example

person_id    child       mom 

1            isabella    null
2            marry       isabella
3            john        marry
4            alfonso     marry
5            sophia      null
6            mark        sophia
7            emma        null
8            michael     emma

NB: a mom can be both a mom and a child e.g : marry has 2 children (john and alfonso) but she is also the daughter of isabella.

Any way to achieve this in sql?

Answer 1

If I am following you correctly, you need a recursive query:

with recursive cte as (
    select t.*, array[person_id] path from mytable t where mom is null
    union all
    select t.*, c.path || t.person_id
    from cte c
    inner join mytable t on t.mom = c.child
)
select * from cte order by path

The idea is to build the path to each row, which you can then use to order the resultset.

Demo on DB Fiddle:

person_id | child    | mom      | path   
--------: | :------- | :------- | :------
        3 | emma     | null     | {3}    
        7 | michael  | emma     | {3,7}  
        5 | sophia   | null     | {5}    
        2 | mark     | sophia   | {5,2}  
        8 | isabella | null     | {8}    
        6 | marry    | isabella | {8,6}  
        1 | john     | marry    | {8,6,1}
        4 | alfonso  | marry    | {8,6,4}