Dinstint by condition, perhaps?

Question

MySQL

There is a table of private messages
Generally
id - id - messages
time - time
text -text
sender - sender
receiver - receiver
s_del - deleted message sender
r_del - deleted message receiver
The problem is that I need to display dialogs. That is, one last message between users.
For example, your user_id is 1. I get a condition where sender = 1 or receiver=1
I get

SELECT * FROM `messages` WHERE `sender`=1 or `receiver`=1 order by id DESC

In general, I sit for an hour in the head of the manual does not come to mind. At least give me a direction... Thanks in advance

Answer 1

SELECT 'test' FROM `messages` WHERE `sender`=1 or `receiver`=1 order by time DESC

Subquery:

The principle of dialogues in VK

Until you bring (more important even for yourself) the task in terms of the database, nothing will work.

Answer 2

SELECT * FROM messages WHERE id IN (SELECT max(id) FROM messages WHERE `sender`=1 or `receiver`=1 GROUP BY sender, receiver)

Answer 3

The condition of sorting by creation time is equivalent to the condition of sorting by id, only the latter is generally faster.

SELECT *
FROM 'messages' 
WHERE ID IN (
  SELECT max(id) 
  FROM `messages` 
  WHERE `sender`=1 or `receiver`=1 
  GROUP BY `sender`, `reciever`
);