mySQL Query: How to choose just one comment from post list?

Question

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?

Answer 1

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