Insert row between two rows in SQL

Question

I have a table like this one that shows which user commented on which parent thread.

ParentID CommentID UserName CommentDateTime 58 58 Vicky 2016-12-02 11:51:07.270 58 61 Billu 2016-12-02 12:35:40.220 58 62 Rakesh 2016-12-02 12:37:42.133 

Assuming a comment is added to the second line, a new comment is created, which is 63

I want to write a sql query that lists the rows in the following order:

 ParentID CommentID UserName CommentDateTime 58 58 Vicky 2016-12-02 11:51:07.270 58 61 Billu 2016-12-02 12:35:40.220 61 63 Rakesh 2016-12-02 13:37:42.133 58 62 Rakesh 2016-12-02 12:37:42.133 

Could you help me write logic / SQL for this?

Answer 1

If I succeed, you want to display the records in the desired order without inserting them into the table. Here we see TREE messages, so I think you should use a recursive CTE . For each node, we create a PATH line, and then sort the nodes using this path line:

 WITH CTE AS ( SELECT ParentID, CommentID, UserName, CommentDateTime, ParentID as ThreadID, CAST(ParentID as varchar(MAX)) as PathStr FROM T WHERE CommentID = T.ParentID UNION ALL SELECT T.ParentID, T.CommentID, T.UserName, T.CommentDateTime, CTE.ThreadID, PathStr+'-' +CAST(T.CommentID as varchar(MAX)) as PathStr FROM T JOIN CTE ON CTE.CommentID = T.ParentID WHERE T.CommentID <> T.ParentID ) SELECT * FROM CTE ORDER BY ThreadID,PathStr 

I added more examples to the example table, here is the result:

 ╔══════════╦═══════════╦══════════╦═════════════════════════╦══════════╦═════════════╗ ║ ParentID ║ CommentID ║ UserName ║ CommentDateTime ║ ThreadID ║ PathStr ║ ╠══════════╬═══════════╬══════════╬═════════════════════════╬══════════╬═════════════╣ ║ 58 ║ 58 ║ Vicky ║ 2016-12-02 11:51:07.270 ║ 58 ║ 58 ║ ║ 58 ║ 61 ║ Billu ║ 2016-12-02 12:35:40.220 ║ 58 ║ 58-61 ║ ║ 61 ║ 63 ║ Rakesh ║ 2016-12-02 13:37:42.133 ║ 58 ║ 58-61-63 ║ ║ 58 ║ 62 ║ Rakesh ║ 2016-12-02 12:37:42.133 ║ 58 ║ 58-62 ║ ║ 158 ║ 158 ║ Vicky ║ 2016-12-02 11:51:07.270 ║ 158 ║ 158 ║ ║ 158 ║ 161 ║ Billu ║ 2016-12-02 12:35:40.220 ║ 158 ║ 158-161 ║ ║ 161 ║ 163 ║ Rakesh ║ 2016-12-02 13:37:42.133 ║ 158 ║ 158-161-163 ║ ║ 161 ║ 164 ║ Rakesh ║ 2016-12-02 13:37:42.133 ║ 158 ║ 158-161-164 ║ ║ 158 ║ 162 ║ Rakesh ║ 2016-12-02 12:37:42.133 ║ 158 ║ 158-162 ║ ╚══════════╩═══════════╩══════════╩═════════════════════════╩══════════╩═════════════╝