Retrieving the last record in each group - group by multiple columns

Question

I'm doing a group of record with the same post_id and sender id, I want each group to return record with the lastest time, I'm using myql version 5, how do I do it.

This is my messages table

expected results:

I used the following query but it did not return the record with the lastest time in each group as my expectation

select * from `messages` group by `post_id`, `sender_id` order by `time` desc

Answer 1

One method uses a correlated subquery:

select m.*
from messages m
where m.time = (select max(m2.time)
                from messages m2
                where (m2.post_id, m2.sender_id) in ( (m.post_id, m.sender_id), (m.sender_id, m.post_id))
               );