私人留言系统[收件箱查询]

Question

I have a table called "message" and I want to show all talk between users (last message)

Table structure:

message_id | user_id | recipient_id | message | status | date

Example rows:

1 | 1 | 2 | Hello | 0 | 2016-03-26 12:00:00
2 | 2 | 1 | Hi | 0 | 2016-03-26 12:05:00
3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
4 | 1 | 2 | How are you? | 0 | 2016-03-26 12:10:00
5 | 2 | 1 | Fine :) | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00

So, result should be (for user_id == 1):

3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
5 | 2 | 1 | Fine :) | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00

Answer 1

First you sort by date, then you group by user_id

    SELECT *
      FROM
         (
                SELECT * from messages
                 WHERE `user_id`=1 or `recipient_id`=1
              ORDER BY `date` DESC
         ) m
  GROUP BY `user_id`

Result:

3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
5 | 2 | 1 | Fine :)                            | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D                              | 0 | 2016-03-26 15:00:00

<kbd>sqlFiddle demo</kbd>