CODEWITHSUNDEEP
mysqlsqlgroup-by
asdfkjasdfjk
asdfkjasdfjk

Reputation: 3894

Group by to same table id

I have a comment table like below

id     comment_id  date
 2        Null        ...
 3         2          ...
 4         2          ...
 5        Null        ...
 6         5          ...
 7         Null       ...
 8         2          ...
 9         5          ...

I want to make a query, probably a group by so that the output looks like below

id     comment_id  date
 2        Null        ...
 3         2          ...
 4         2          ...
 8         2          ...
 5        Null        ...
 6         5          ...
 9         5          ...
 7         Null       ...

comment_id basically refer to the 'id' column. That is these are the reply to the comments. Now I would like to get the comments for example comment2 and reply to comment2 i.e comment3 , comment4 and comment8 grouped together, they should be ordered by date. Similarly, comment5 and reply to it are comment6, comment9 grouped together and so on. What would be the query?

Upvotes: 2

Views: 172

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think this does what you want:

select c.*
from comments c
order by coalesce(comment_id, id),
         (case when comment_id is null then 1 else 2 end),
         id;

The keys do the following:

  • Associate all comments with their ids.
  • Put the main comment first.
  • Order the rest by id.

Upvotes: 3

Related Questions