Help with an SQL query on a single (comments) table (screenshot included)

Question

Please see screenshot:

Goal:

• id like to have comments nested 1 level deep
• The comments would be arranged so that rating of the parent is in descending order
• the rating of the children comments is irrelevant

The left hand side of the screenshot shows the output that Id like. The RHS shows the table data. All of the comments are held in 1 table.

Im a beginner with SQL queries, the best I can do is:

SELECT * 
FROM [Comments] 
WHERE ([ArticleId] = @ArticleId) ORDER BY [ThreadId] DESC, [DateMade] 

This somewhat does the job, but it obviously neglects the rating. So the above statement would show output where Bobs Comment and all of the children comments are before Amy's and her childrens comments. How can I run this query correctly?

Answer 1

I would think something like this might come close.

SELECT child.* FROM Comments parent, Comments child WHERE parent.id = child.parent_id ORDER BY parent.rating DESC, DateMade DESC

This is going to have weird results if the rating of 2 threads is the same, so you'll have to order on that too somehow.

Answer 2

SELECT c.* 
FROM Comments AS c
JOIN Comments AS tr ON tr.threadID = c.threadID 
 AND tr.ArticleId = c.ArticleId AND tr.isParent = 1
WHERE (c.ArticleId = @ArticleId) 
ORDER BY tr.Rating DESC, c.ThreadId DESC, c.DateMade 

You have to join the table with itself to get the parents rating then you sort by the thread rating first, then by thread id in case many threads have the same rating, and then by comment date.

You dont have to shove int sorting by c.isParent DESC before date cause this is the oldest comment in the thread.