MySQL 8.0

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

create table person (id int, `date` date);
insert into person values 
    (1, current_date), 
    (2, current_date - interval 2 day), 
    (3, current_date - interval 6 day),
    (4, current_date - interval 5 day),
    (5, current_date - interval 4 day),
    (6, current_date - interval 3 day),
    (7, current_date - interval 1 day);

Records: 7  Duplicates: 0  Warnings: 0


select * from person order by id


id
date


1
2024-05-06

2
2024-05-04

3
2024-04-30

4
2024-05-01

5
2024-05-02

6
2024-05-03

7
2024-05-05


select *
from (
    select p.*,
        row_number() over(order by id desc) rn_id,
        row_number() over(order by date desc) rn_dt
    from person p
) p
order by case when rn_id <= 3 then rn_id else 9 end, rn_dt
limit 6


id
date
rn_id
rn_dt


7
2024-05-05
1
2

6
2024-05-03
2
4

5
2024-05-02
3
5

1
2024-05-06
7
1

2
2024-05-04
6
3

4
2024-05-01
4
6

id	date
1	2024-05-06
2	2024-05-04
3	2024-04-30
4	2024-05-01
5	2024-05-02
6	2024-05-03
7	2024-05-05