CODEWITHSUNDEEP
sql.netsql-servert-sqllogic
SmartestVEGA
SmartestVEGA

Reputation: 8889

SQL Query logic for order by

I am facing issues for creating a order by logic in SQL query:

Query is shown below:

WITH CTE AS ( SELECT CommentID ,
                CommentUserName,
                 ReplyCommentID ,
                CommentID AS ThreadID ,
                CAST( CommentID AS VARCHAR( MAX ) ) AS PathStr,
                HtmlComment ,
                CommentPostDocumentID ,
                 CommentIsApproved,
                CommentDate 

         FROM Blog_CommentDetails AS T WITH(NOLOCK)
         WHERE ReplyCommentID IS NULL
         UNION ALL
         SELECT T.CommentID ,
                T.CommentUserName,
                T.ReplyCommentID ,
                CTE.ThreadID ,
                PathStr + '-'+ CAST( T.ReplyCommentID AS VARCHAR( MAX ) ) AS PathStr,
                T.HtmlComment ,
                t.CommentPostDocumentID ,
                 t.CommentIsApproved,
                T.CommentDate 

         FROM Blog_CommentDetails AS T WITH(NOLOCK)
         JOIN CTE 
         ON T.ReplyCommentID = CTE.CommentID
         WHERE T.ReplyCommentID IS NOT NULL)

SELECT *
    FROM CTE
    WHERE CommentPostDocumentID = 15 AND CommentIsApproved=1
    ORDER BY ThreadID, PathStr ,
            CommentDate DESC;

Its shows the following result:

Query Result

But the expected result is: enter image description here

Change is , I need to insert the record just after commentid - 144 because reply commentid and commentid is same.

Means whenever there is a matching commentid and replycommentid , in this case its 144, then the row with replycommentid should come down to the row having the commentid 144.

Query listing for the answer:

query result for DVT

Upvotes: 1

Views: 135

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

This is deep first approach and can be done using ORDER BY hierarchyid values thus:

(1) ORDER BY ThreadId, CONVERT(HIERARCHYID, '\' + REPLACE(PathStr, '-', '\') + '\'), CommentDate

or

(2) Within recursive CTE I would generate PathStr as hirarchyid values.

Note: I didn't tested this solution. Note #2: Sorry for bad formatting: I'm writting this answer on my phone.

Upvotes: 0

DVT
DVT

Reputation: 3127

How about sorting by the string of commentID of the parent record combination with current record

WITH CTE AS ( SELECT CommentID ,
                CommentUserName,
                 ReplyCommentID ,
                CommentID AS ThreadID ,
                CAST( CommentID AS VARCHAR( MAX ) ) AS PathStr,
                HtmlComment ,
                CommentPostDocumentID ,
                 CommentIsApproved,
                CommentDate ,
                CAST('' AS VARCHAR(MAX)) AS NewPathStr -- added
         FROM Blog_CommentDetails AS T WITH(NOLOCK)
         WHERE ReplyCommentID IS NULL
         UNION ALL
         SELECT T.CommentID ,
                T.CommentUserName,
                T.ReplyCommentID ,
                CTE.ThreadID ,
                PathStr + '-'+ CAST( T.ReplyCommentID AS VARCHAR( MAX ) ) AS PathStr,
                T.HtmlComment ,
                t.CommentPostDocumentID ,
                 t.CommentIsApproved,
                T.CommentDate,              
                CTE.NewPathStr+'--'+CAST(cte.CommentID AS VARCHAR(MAX)) + '-' + CAST(t.CommentID AS VARCHAR(MAX)) AS NewPathStr  --added
         FROM Blog_CommentDetails AS T WITH(NOLOCK)
         JOIN CTE 
         ON T.ReplyCommentID = CTE.CommentID
         WHERE T.ReplyCommentID IS NOT NULL)

SELECT *  -- You might have to remove NewpathStr if you do not want to see it
    FROM CTE
    WHERE CommentPostDocumentID = 15 AND CommentIsApproved=1
    ORDER BY ThreadID, NewPathStr, PathStr ,
            CommentDate DESC;

Upvotes: 1

Related Questions