SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE hireo_messages (
  `id` INTEGER,
  `receiver_id` INTEGER,
  `sender_id` INTEGER,
  `message_content` VARCHAR(9),
  `created_at` TEXT
);
INSERT INTO hireo_messages
  (`id`, `receiver_id`, `sender_id`, `message_content`, `created_at`)
VALUES
  ('1', '1', '2', 'some text', '2020-08-11 13:29:47.342944'),
  ('3', '3', '2', 'some text', '2020-08-11 13:44:55.499638'),
  ('4', '2', '1', 'some text', '2020-08-11 14:20:55.499638'),
  ('5', '1', '2', 'some text', '2020-08-12 05:06:05.497500'),
  ('6', '2', '5', 'some text', '2020-08-12 10:39:31.234082'),
  ('7', '4', '1', 'some text', '2020-08-14 13:25:19.357876');
select id, receiver_id, sender_id, message_content, created_at 
from (
  select *,
    max(created_at) over (partition by min(receiver_id, sender_id), max(receiver_id, sender_id)) last_date
  from hireo_messages
  where 2 in (receiver_id, sender_id) 
)
where created_at = last_date
order by id desc


id
receiver_id
sender_id
message_content
created_at


6
2
5
some text
2020-08-12 10:39:31.234082

5
1
2
some text
2020-08-12 05:06:05.497500

3
3
2
some text
2020-08-11 13:44:55.499638


select h.* from hireo_messages h
where 2 in (receiver_id, sender_id)
and not exists (
  select 1 from hireo_messages
  where min(receiver_id, sender_id) = min(h.receiver_id, h.sender_id)
    and max(receiver_id, sender_id) = max(h.receiver_id, h.sender_id)
  and created_at > h.created_at
)  
order by h.id desc


id
receiver_id
sender_id
message_content
created_at


6
2
5
some text
2020-08-12 10:39:31.234082

5
1
2
some text
2020-08-12 05:06:05.497500

3
3
2
some text
2020-08-11 13:44:55.499638

id	receiver_id	sender_id	message_content	created_at
6	2	5	some text	2020-08-12 10:39:31.234082
5	1	2	some text	2020-08-12 05:06:05.497500
3	3	2	some text	2020-08-11 13:44:55.499638