Published on: 12 May, 2016

How to sort comments with nested replies in SQL Server?

Posted by andy | 908 views 0 likes 0 favourites 0 comments
Tumblr Digg Google Plus

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.

How to sort comments with nested replies

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 ))

Example of sorting comments with nested replies

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.
Share your article with us and add your own google adsense account to earn extra money. Plus, you can promote a link back to your site.
Related Articles
Published on: 25 September, 2017
When creating a function dynamically, it is a best practice to perform a check if a particular function you want to create has already existed in a database. To perform a check of an existing function you can use the following EXISTS keyword. If the function does not exist then perform a creation of the function.
Published on: 14 April, 2016
Before running an SQL script to create a table dynamically, it would be a good step if you perform a checking if a table already exists. If it does, you may want to skip creating the table otherwise you will get an error exception.
Related Tutorials
Published on: 27 September, 2013
Learn what is UpperCase function in SQL server and how to use UCASE() in sql query.
Published on: 26 September, 2013
Learn what is LCASE() function in SQL server and how to use LCASE() in sql query.
Published on: 25 September, 2013
Learn what is LEN() function in SQL server and how to use LEN() in sql query.
Published on: 24 September, 2013
Learn what is COUNT() function in SQL server and how to use COUNT() in sql query.
Published on: 23 September, 2013
Learn what is AVG() function in SQL server and how to use AVG() in sql query.