MySQL retrieves last record for group

Question

I have a social networking site and am struggling with the request. I have a message table that stores all user messages, and then a post_comments table that contains all the comments for the message. I am trying to find the last comment in the mail from the post_comments table. The post_comments table has the following columns:

post_comment_id, post_id, writer_user_id, post_comment_content, Date and time

I grouped the results of post_id like this:

SELECT * FROM post_comments GROUP BY post_id 

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

Answer 1

GROUP BY is intended for use with aggregation functions; otherwise, it arbitrarily selects one row for each group. Your solution (above and in your own answer) works because MySQL seems to keep the first row of each group, but you cannot guarantee that this will always happen.

You can get the date of the last 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 last 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 

Answer 2

Since so many questions come up with "I want the last entry for ..." and have some time. If the table in question is an automatic increment, and the date / time stamp will ALWAYS remain in the correction of sequential time ... that is: the user does not have the right to edit or otherwise change the time stamp in the record to place today's record with the date 3 weeks ago .. Therefore, many will continue to try to get the latest date and re-join the insecure key. Just get the maximum ID key for the message and use it ... I will have an index for the message identifier and the main auto-increment key.

 select P2.* from ( select post_id, max( post_comment_id ) as LastPost from post_comments group by post_id ) LastPerPost JOIN post_comments P2 on LastPerPost.LastPost = P2.post_comment_id 

Answer 3

Sorted! I did it like this:

 SELECT * FROM ( SELECT * FROM post_comments ORDER BY datetime DESC ) AS post_comments GROUP BY post_id