How to write query (sql server) for loading comments & replies like facebook?

I had comment table: (us开发者_如何学Pythoning for both comment & reply)

-commentId
-parentId
-message

parentId=0: comment
parentId != 0: reply

Like facebook, i want to query (sql server) to get comments + replies. But with replies, i just want to get 3 latest replies. and at the website, i will display "show all replies" to fetch by ajax call.

So, how can i write this query: For example: http://i.stack.imgur.com/mASRC.png

Any help? please!!!

---

declare @Comment table(commentId int, parentId int, message varchar(20))

insert into @Comment 
select 1, 0,'my comment 1' union all
select 2, 0,'mu comment 2' union all
select 3, 1,'reply 1.1'    union all
select 4, 1,'reply 1.2'    union all
select 5, 1,'reply 1.3'    union all
select 6, 1,'reply 1.4'    union all
select 7, 1,'reply 1.5'    union all
select 8, 2,'reply 2.1'    union all
select 9, 2,'reply 2.2'    union all
select 10,2,'reply 2.3'    union all
select 11,2,'reply 2.4'

;with C as
(
  select commentId,
         parentId,
         message,
         row_number() over(partition by parentId order by commentId desc) as rn
  from @Comment
)
select C.commentId,
       C.parentId,
       C.message
from C
where C.parentId = 0 or C.rn <= 3
order by C.commentId

Result:

commentId   parentId    message
----------- ----------- --------------------
1           0           my comment 1
2           0           mu comment 2
5           1           reply 1.3
6           1           reply 1.4
7           1           reply 1.5
9           2           reply 2.2
10          2           reply 2.3
11          2           reply 2.4

---

For any hierarchical structure you need a recursive query. Because if you don't limit the number of replies, you don't know how many levels of reply is stored in your table. Your table is like these tables:

    CategoryId & CategoryTitle & ParentCategoryId

or

    EmployeeId & EmployeeName & ManagerId

But in none of these tables, you don't know how many levels exist in your table. So, you have to write a recursive query.

---

https://web.archive.org/web/20080206204801/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-limit-the-number-of-rows-returned-in-my-resultset.html

This article looks like it might be helpful in constraining the number of rows in a result set.

Have you tried something like:

SELECT TOP 3 commentID, parentId, message
FROM commentTable
WHERE parentId IN(0,1)
ORDER BY message DESC;

If you have the power to control the layout of your database, you might want to break your table apart into two tables on individual themes: one for messages, and one for replies. You could then add a date-time field to the replies table and select the TOP 3 rows from it, based off of the posting date, with order desc.