sort child / parent in same table

user_9783502 0
Viewed 110

I have a MySQL table which is as follows:

member_id | name        | parent     |....
   1      | john        | 0          |
   2      | alex        | 0          |
   3      | nikita      | 1          |
   4      | sarah       | 1          |
   .
   .
   .

i want to sort with parrent. i try this but not work:

SELECT * FROM `members` ORDER BY COALESCE(`parrent`,`member_id`),`parrent` !=0,`member_id`

all child sorted, but parent not with them.

i want this result :

member_id | name        | parent     |....
   2      | alex        | 0          |
   1      | john        | 0          |
   3      | nikita      | 1          |
   4      | sarah       | 1          |
   .
   .
   .

parents first and then childs.

is there a better solution to implement this table? I need a table that contain families

coalesce parent-child mysql sql
1 Answers

coalesce() doesn't work because the parent is 0. You can use nullif() instead:

ORDER BY COALESCE(NULLIF(parent, 0), member_id),
        (parent = 0) DESC,
        member_id

Notice that I use (parent = 0) DESC for the second key. I prefer the logic to express the matches we want first, with DESC to put true values before false ones.

user_11440350
Related