[SOLVED] SQL - Paging comments

SQL - Paging comments

I have the Table Comment below:

CREATE TABLE Comment
(
     CommentID int,
     Username nvarchar(50),
     Content nvarchar(255),
     ReplyID int,
     primary key (CommentID),
)

ALTER TABLE dbo.Comment
ADD FOREIGN KEY (CommentID) REFERENCES dbo.Comment (CommentID) ON DELETE NO ACTION ON UPDATE NO ACTION,

And I want to query paging comment in detail product with data:

+----+----------+-------------+---------+
| ID | Username |   Content   | ReplyID |
+----+----------+-------------+---------+
|  1 | UserA    | hello       | null    |
|  2 | UserB    | hello       | null    |
|  3 | UserC    | Hi UserA    | 1       |
|  4 | UserD    | Hello World | null    |
|  5 | UserE    | Hi UserB    | 2       |
+----+----------+-------------+---------+

How can I show comments with paging, and displayPerPage = 2, example:

UserA: Hello

       UserC: Hi UserA

UserB: Hello

       UserE: Hi UserB

>>More Comments<<

Any help will be appreciated.

Solution 1: using Outer Join

Solution

You could use a query similar to the following to get the Comments in the correct order:

select case when c.ReplyId is not null then '        ' else '' end
     + UserName + ': ' + c.content Line
  from Comment c
 order by IsNull(c.ReplyId, c.CommentId), c.commentId