CODEWITHSUNDEEP
sqlpostgresql
Yash Ojha
Yash Ojha

Reputation: 818

Retrieving posts with latest 2 comments of each post in postgres

I have a separate post table and a comment table. I am fetching 15 posts (pagination) and along with posts I want to fetch the latest 2 comments of each post. is there any efficient way to do so in a single or less Postgres-sql queries.

Post table - id, text, pics, likes, ownerId

Comment table - id, text, userId, postId

User table - id, profilePic, username

Upvotes: 0

Views: 433

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The query you want would seem to be:

select p.*, c.*
from post p left join
     (select c.*,
             row_number() over (partition by postid order by id desc) as seqnum
      from comments c
     ) c
     on p.postid = c.postid and seqnum <= 2

Upvotes: 1

Related Questions