MySQL 8.0

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

create table chat (
    id int not null primary key,
    user_id int not null,
    user_id_to int not null,
    message varchar(100),
    send_date datetime
)
insert into chat (id, user_id, user_id_to, message, send_date) values
(1,  1, 2, 'test 1',  '2023-02-01'),
(2,  2, 1, 'test 2',  '2023-02-02'),
(3,  3, 1, 'test 3',  '2023-02-03'),
(4,  1, 2, 'test 4',  '2023-03-05'),
(5,  2, 1, 'test 5',  '2023-03-08'),
(6,  3, 1, 'test 6',  '2023-03-10'),
(7,  4, 1, 'test 7',  '2023-03-11'),
(8,  5, 1, 'test 8',  '2023-03-13'),
(9,  5, 1, 'test 9',  '2023-03-15'),
(10, 5, 1, 'test 10', '2023-03-20'),
(11, 1, 3, 'test 11', '2023-03-22')

Records: 11  Duplicates: 0  Warnings: 0


select *
from chat as newer
where (
    newer.user_id = 1 or
    newer.user_id_to = 1
) and not exists (
    select 1
    from chat as older
    where (
        older.user_id = newer.user_id and older.user_id_to = newer.user_id_to or
        older.user_id = newer.user_id_to and older.user_id_to = newer.user_id
    )
    and older.send_date > newer.send_date
)


id
user_id
user_id_to
message
send_date


5
2
1
test 5
2023-03-08 00:00:00

7
4
1
test 7
2023-03-11 00:00:00

10
5
1
test 10
2023-03-20 00:00:00

11
1
3
test 11
2023-03-22 00:00:00


with cte1 as (
    select
        chat.*,
        case when user_id < user_id_to then user_id else user_id_to end as user1,
        case when user_id < user_id_to then user_id_to else user_id end as user2
    from chat
), cte2 as (
    select
        cte1.*,
        row_number() over (partition by user1, user2 order by send_date desc) as rn
    from cte1
)
select *
from cte2
where (user1 = 1 or user2 = 1) and rn = 1


id
user_id
user_id_to
message
send_date
user1
user2
rn


5
2
1
test 5
2023-03-08 00:00:00
1
2
1

11
1
3
test 11
2023-03-22 00:00:00
1
3
1

7
4
1
test 7
2023-03-11 00:00:00
1
4
1

10
5
1
test 10
2023-03-20 00:00:00
1
5
1

id	user_id	user_id_to	message	send_date
5	2	1	test 5	2023-03-08 00:00:00
7	4	1	test 7	2023-03-11 00:00:00
10	5	1	test 10	2023-03-20 00:00:00
11	1	3	test 11	2023-03-22 00:00:00