I have one level hierarchy table structure using Adjacency List Model. It could be summarized as follows:
id parent_id title created_at
1 null name1 2017-05-30 08:05:00
2 null name2 2017-05-30 08:15:00
3 1 another name 2017-05-30 09:00:00
4 1 new name 2017-05-30 08:06:00
5 2 new title 2017-05-30 08:18:04
6 null lorem 2017-05-30 08:04:00
What I need to get is an sql query that returns every row of null parent_id i.e the parent followed by its children ordered by created_at Something like the following:
id parent_id title created_at
6 null lorem 2017-05-30 08:04:00
1 null name1 2017-05-30 08:05:00
4 1 new name 2017-05-30 08:06:00
3 1 another name 2017-05-30 09:00:00
2 null name2 2017-05-30 08:15:00
5 2 new title 2017-05-30 08:18:04
I have tried
SELECT COALESCE(`parent_id`, `id`) as pid, title, created_at
FROM `items`
ORDER BY created_at
But it does not succeeded the another name record came separately at the end of the result set.
This is sql fiddle for the case
Notice In the real case the id is an UUID string.