Do you need to sort comments with nested replies in SQL Server? Well this can be easily done by combining the same table using the UNION ALL join to populate the comments content. There is a trick to used to sort the comments which use the combination of a record ID field with the row number index.

Let says we have the following table structure for the comments data.

To sort the comment replies in nested order, you can use the following sql query.

DECLARE @ThreadID INT = 1

With CommentList As (
		Select	Comments_Parent.*,
				CONVERT(nvarchar(100), Row_Number() OVER ( ORDER BY Comments_Parent.CommentId )) as OrderPath
		FROM Comments Comments_Parent
		Where CommentReplyID = 0
		AND ThreadID = @ListingID
		AND Approved = 1
		
		UNION ALL
		
		SELECT	Sub_Comments.*,
				CONVERT(nvarchar(100), CL.OrderPath + '.' + Cast( Row_Number() over ( order by Sub_Comments.CommentId ) as nvarchar(100)))  As CommentLevel
		FROM Comments Sub_Comments
		INNER JOIN CommentList CL
		ON Sub_Comments.CommentReplyID = CL.CommentID
		WHERE Sub_Comments.Approved = 1
	)

SELECT * FROM CommentList
ORDER BY CONVERT(INT,SUBSTRING (CommentLevel, CASE WHEN charindex('.',CommentLevel,0)=0 THEN 1 ELSE 0 END, CASE WHEN charindex('.',CommentLevel,0)=0 THEN LEN(CommentLevel) else charindex('.',CommentLevel,0) end ))