MySQL retrieve latest record for Group

Question

I have a social networking site and am struggling with a query. I have a posts table that holds all of the users posts and then a post_comments table that holds all comments on a post. I am trying to find the latest comment by post from post_comments table. The post_comments table has the following columns:

post_comment_id, post_id, writer_user_id, post_comment_content, datetime

I have grouped the results by post_id like so:

SELECT * FROM post_comments GROUP BY post_id

This almost does what I want but it always returns the oldest comment for each post not the newest one. How can I get it to return the newest comment for each post?

Answer 1

GROUP BY is intended to be used with aggregating functions, otherwise it arbitrarily selects one row per group. Your solution (above and in your self-answer) works because MySQL seems to be keeping the first row of each group, but you're not guaranteed that this will always happen.

You can get the date of the latest comment for each post_id like this.

select post_id, MAX(datetime) as latest from post_comments group by post_id

Use it to select the latest comment:

SELECT t1.* FROM post_comments AS t1
JOIN (
    SELECT post_id, MAX(datetime) AS latest FROM post_comments GROUP BY post_id
) AS t2
ON t1.post_id = t2.post_id AND t1.datetime = t2.latest