I have a table like this, which 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
If suppose a comment is made to the 2nd row, a new commentid is generated which is 63
I want to write a sql query which list the rows in the below 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 please help me in writing a logic/SQL for this?
If I've got it right you want to output records in the desired order not insert them into the table. Here we see a TREE of posts so I think you should use a recursive CTE. For each node we build a PATH string and then sort nodes using this path string:
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've added more nodes to example table so 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 ║
╚══════════╩═══════════╩══════════╩═════════════════════════╩══════════╩═════════════╝