SQL query to fetch previous record using previous id column-CodePudding

I am trying to write a SQL query where I can query the latest and previous/last two comments. I have table structures as below -

Record Table

Id	RecordName	LatestCommentId
1	Record 1	3
2	Record 2	6
3	Record 3	7

Comment Table

Id	Comment	PreviousCommentId
1	Comment 1	NULL
2	Comment 2	1
3	Comment 3	2
4	Comment A	NULL
5	Comment B	4
6	Comment C	5
7	Comment P	NULL

Record table references Comments table to get the latest comment id for that record. I want to create a query where I get the latest as well as previous comments like -

RecordName	LatestComment	PreviousComment	PreviousComment1
Record 1	Comment 3	Comment 2	Comment 1
Record 2	Comment C	Comment B	Comment A
Record 3	Comment P	NULL	NULL

I will be storing only last two comments along with latest comment.

I was also exploring the Recursive CTE option and here is what I was able to create -

WITH COMMENTS_HISTORY AS (
  SELECT
    Id,
    CommentBody,
    PreviousCommentId
  FROM 
    Comment
  WHERE 
    PreviousCommentId IS NULL
 
  UNION ALL
   
  SELECT
    c.Id,
    c.CommentBody,
    c.PreviousCommentId
  FROM 
    Comment c, COMMENTS_HISTORY ch
  WHERE c.PreviousCommentId = ch.Id
)
SELECT
    c.Id,
    c.CommentBody AS 'Latest Comment',
    ch.CommentBody AS 'Comment 1'
FROM 
    COMMENTS_HISTORY ch
LEFT JOIN 
    Comment c
ON
    ch.PreviousCommentId = c.Id

CodePudding user response：

You just need to join the comment table 3 times, one per comment column

SELECT
    r.recordname,
    c.comment AS LatestComment,
    pc.comment AS PreviousComment,
    pc1.comment AS PreviousComment1
FROM record r
LEFT JOIN comment c ON c.id = r.latestcommentid
LEFT JOIN comment pc ON pc.id = c.previouscommentid
LEFT JOIN comment pc1 ON pc1.id = pc.previouscommentid

Output

recordname	LatestComment	PreviousComment	PreviousComment1
Record 1	Comment 3	Comment 2	Comment 1
Record 2	Comment C	Comment B	Comment A
Record 3	Comment P	null	null

db<>fiddle here