Mysql - Query For Thread List Of Messages With Specific Conditions

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

Solution 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
           GROUPBY 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
ORDERBY 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?