1

Table post: id

Table post_comments id, post_id, comment, created_at

REQUEST:

SELECT * from post
left join post_comments on post_comments.post_id = post.id

This request return all of the comments for every post. How could I limit this query for return just a last comment only?

GMB
  • 216,147
  • 25
  • 84
  • 135
mvn.2047
  • 362
  • 1
  • 10

1 Answers1

3

There are many ways to adress this top 1 per group question.

One method is to filter with a correlated subquery that returns the date of the latest comment for the current post:

select p.*, c.*
from post p
inner join post_comments c 
    on  c.post_id = p.id
    and c.created_at = (
        select max(c1.created_at) from post_comments c1 where c1.post_id = c.post_id
    )

I also like the anti-left join solution, that ensures that there is no other comment for the same post with a later creation date:

select p.*, c.*
from post p
inner join post_comments c on c.post_id = p.id
left join post_comments c1 on c1.post_id = p.id and c1.created_at > c.created_at
where c1.id is null
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you for your support. I like your left join solution - It is very simple and efficiency. I mark your answer, appreciate you have helped me. – mvn.2047 Jan 21 '20 at 23:44
  • Welcome @esff! Glad that it helped. – GMB Jan 21 '20 at 23:52