MySQL ordering for main and sub elements in a single query

June 8, 2024

My MySQL table looks like this:

_id	_masterid
17	null
18	17
19	17
20	null
21	null
22	20
23	17
24	20
25	20

Is it possible to order it with a single query so we get this results as output?

_id	_masterid
17	null
18	17
19	17
23	17
20	null
22	20
24	20
25	20
21	null

_ids without _masterid are main comments, and _ids with _masterids are replies. So ideally I’d like to order comment and its replies bunched up together, before proceeding to the next comment.

Thanks!

>Solution :

Yes, it is possible:

select
    *
from
    `my_table`
order by
    coalesce(`_masterid`, `_id`),
    `_masterid` is not null

The coalesce(_masterid, _id) part returns either _masterid if it’s not NULL, or _id otherwise. Then you put the parent thread first by placing _masterid being NULL before the non-NULL ones.