MySQL 8.0

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

CREATE TABLE posts (id INT, thread_id INT, `time` DATETIME);
INSERT INTO posts VALUES
(1,0,'2021-05-01'),
(2,0,'2021-05-02'),
(3,0,'2021-05-03'),
(4,2,'2021-05-04'),
(5,0,'2021-05-05'),
(6,2,'2021-05-06'),
(7,3,'2021-05-07'),
(8,0,'2021-05-08'),
(9,8,'2021-05-09');
SELECT * FROM posts;


id
thread_id
time


1
0
2021-05-01 00:00:00

2
0
2021-05-02 00:00:00

3
0
2021-05-03 00:00:00

4
2
2021-05-04 00:00:00

5
0
2021-05-05 00:00:00

6
2
2021-05-06 00:00:00

7
3
2021-05-07 00:00:00

8
0
2021-05-08 00:00:00

9
8
2021-05-09 00:00:00


SELECT p1.*,
       p3.time last_thread_time,
       COALESCE(p3.time, p1.time) time_for_sorting
FROM posts p1
LEFT JOIN LATERAL ( SELECT MAX(p2.time) time
                    FROM posts p2
                    WHERE p2.thread_id = p1.id ) p3 ON TRUE


id
thread_id
time
last_thread_time
time_for_sorting


1
0
2021-05-01 00:00:00
null
2021-05-01 00:00:00

2
0
2021-05-02 00:00:00
2021-05-06 00:00:00
2021-05-06 00:00:00

3
0
2021-05-03 00:00:00
2021-05-07 00:00:00
2021-05-07 00:00:00

4
2
2021-05-04 00:00:00
null
2021-05-04 00:00:00

5
0
2021-05-05 00:00:00
null
2021-05-05 00:00:00

6
2
2021-05-06 00:00:00
null
2021-05-06 00:00:00

7
3
2021-05-07 00:00:00
null
2021-05-07 00:00:00

8
0
2021-05-08 00:00:00
2021-05-09 00:00:00
2021-05-09 00:00:00

9
8
2021-05-09 00:00:00
null
2021-05-09 00:00:00


SELECT p1.*,
       COALESCE(p3.time, p1.time) last_thread_time
FROM posts p1
LEFT JOIN LATERAL ( SELECT MAX(p2.time) time
                    FROM posts p2
                    WHERE p2.thread_id = p1.id ) p3 ON TRUE
WHERE p1.thread_id = 0
ORDER BY COALESCE(p3.time, p1.time);


id
thread_id
time
last_thread_time


1
0
2021-05-01 00:00:00
2021-05-01 00:00:00

5
0
2021-05-05 00:00:00
2021-05-05 00:00:00

2
0
2021-05-02 00:00:00
2021-05-06 00:00:00

3
0
2021-05-03 00:00:00
2021-05-07 00:00:00

8
0
2021-05-08 00:00:00
2021-05-09 00:00:00

id	thread_id	time
1	0	2021-05-01 00:00:00
2	0	2021-05-02 00:00:00
3	0	2021-05-03 00:00:00
4	2	2021-05-04 00:00:00
5	0	2021-05-05 00:00:00
6	2	2021-05-06 00:00:00
7	3	2021-05-07 00:00:00
8	0	2021-05-08 00:00:00
9	8	2021-05-09 00:00:00

id	thread_id	time	last_thread_time	time_for_sorting
1	0	2021-05-01 00:00:00	null	2021-05-01 00:00:00
2	0	2021-05-02 00:00:00	2021-05-06 00:00:00	2021-05-06 00:00:00
3	0	2021-05-03 00:00:00	2021-05-07 00:00:00	2021-05-07 00:00:00
4	2	2021-05-04 00:00:00	null	2021-05-04 00:00:00
5	0	2021-05-05 00:00:00	null	2021-05-05 00:00:00
6	2	2021-05-06 00:00:00	null	2021-05-06 00:00:00
7	3	2021-05-07 00:00:00	null	2021-05-07 00:00:00
8	0	2021-05-08 00:00:00	2021-05-09 00:00:00	2021-05-09 00:00:00
9	8	2021-05-09 00:00:00	null	2021-05-09 00:00:00