mysql - query for thread list of messages with specific conditions

Question

I have three tables: user, request and message. I would like to get something like the thread list that android/ios message app shows when launched.

User

id  username
1   a
2   b
3   c

Request

id
1
2
3

Message

Id  request_id     sender_id   receiver_id message timestamp
1   1       1       2       asd 2013-06-10 06:45
2   1       1       3       sdf 2013-06-10 06:42
3   2       1       1       dfg 2013-06-10 06:41
4   2       1       2       fgh 2013-06-11 06:40
5   2       1       2       ghj 2013-06-12 07:45
6   2       2       1       jkl 2013-06-10 06:45
7   3       3       1       zxc 2013-06-10 03:45
8   3       3       1       xcv 2013-06-10 05:45

What I would like to get is something like this:

Thread

request_id  sender_name receiver_name   last_message    last_timestamp
1       a       b       asd     2013-06-10 06:45
1       a       c       sdf     2013-06-10 06:42
2       a       b       ghj     2013-06-12 07:45
2       a       a       dfg     2013-06-10 06:41
3       c       a       xcv     2013-06-10 05:45

Here, request_id + sender_id + receiver_id is unique for each row and last_message, last_timestamp shows the latest entry from the message table. The order will be last_timestamp descending. How do I get this table?

Answer 1

You want the groupwise maximum, which can be found by joining the Messages table to a subquery that identifies the identifying (maximal) timestamp for each group:

SELECT   Message.request_id,
         Sender.username   AS sender_name,
         Receiver.username AS receiver_name,
         Message.message   AS last_message,
         Message.timestamp AS last_timestamp
FROM     Message NATURAL JOIN (
           SELECT   request_id,
                    sender_id,
                    receiver_id,
                    MAX(timestamp) timestamp
           FROM     Message
           GROUP BY request_id, sender_id, receiver_id
         ) t
    JOIN User Sender   ON   Sender.id = Message.sender_id
    JOIN User Receiver ON Receiver.id = Message.receiver_id
ORDER BY Message.request_id, last_timestamp DESC

See it on sqlfiddle.

Note that the order of my resultset differs from that expected in your question for the reasons highlighted in my comment above:

You say that "the order will be last_timestamp descending", but in the given example the message 'ghj' (sent two days after all the other messages) appears not only in the middle of all the records but furthermore in the middle of all those with the same request_id too. Please clarify the desired sort order of the resultset?