Select the Latest Message Between the communication of two user in mysql

Question

I have a simple table maintaining messages between users. The table structure looks like ``` sender receiver message sendtime 1 2 m1 2012-01-01 12:12:12 2 1 m2 2012-01-01 12:50:20 1 2 m3 2012-01-01 12:55:55 1 3 m4 2012-01-02 05:05:05 1 4 m5 2012-01-05 05:20:20 4 1 m6 2012-01-06 06:05:00 4 1 m7 2012-01-07 11:11:11 2 4 m8 2012-01-08 05:01:01 ``` Now, for the user with ID 1, I need the result like this ``` sender receiver message sendtime 1 2 m3 2012-01-01 12:55:55 1 3 m4 2012-01-02 05:05:05 4 1 m7 2012-01-07 11:11:11 ``` That is I need the recent messages for the particular user whether he is sender or receiver. Though, it seem easy, I cannot find a way to write a single mysql query. Also, I want suggestion if for this kind of solution, my table design is poor. Note: One thing, which I think I should mention is, for instance there are multiple communication between user 1 and user 2. I need the latest communication between them two whether user 1 is a sender or receiver, only the recent…

Accepted Answer

```
SELECT data.* FROM 
(SELECT MAX(sendtime) AS sendtime 
         FROM data 
         WHERE 1 IN (sender,receiver)
         GROUP BY IF (1 = sender,receiver,sender)) AS latest
LEFT JOIN data ON latest.sendtime = data.sendtime AND 1 IN (data.sender, data.receiver)
GROUP BY IF (1 = data.sender,data.receiver,data.sender)
```

I recommend to use unique sequence id in your table to avoid external `GROUP BY`. If you have incrementing unique `message_id` (i.e. bigger `message_id` corresponds to later `sendtime`), the query would be much simpler:

```
SELECT data.* FROM 
(SELECT MAX(message_id) AS message_id 
         FROM data 
         WHERE 1 IN (sender,receiver)
         GROUP BY IF (1 = sender,receiver,sender)) AS latest
LEFT JOIN data USING(message_id)
```

Select the Latest Message Between the communication of two user in mysql

Solution

Problem