Mysql Question posted in
The official MySQL documentation can be found here.

MySQL ordering for main and sub elements in a single query

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!

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    I would recommend to fill parent (_masterid) with self value.

    So whenever you initiate the comment or whatever topic like: (17, null), (20, null) you do (17, 17), (20, 20).
    This approach will allow you to do simple order with regular indexes in use:

    SELECT _id, _masterid
FROM my_table
ORDER by _masterid, _id

    The approach of Robo Robok is good. But it will have small performance issue because it uses the function result for ordering which is slower than regular indexes.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search