CODEWITHSUNDEEP
mysqlsqlhierarchy
SaidbakR
SaidbakR

Reputation: 13534

MySQL select parent followed by its children and ordered by

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.

Upvotes: 2

Views: 1454

Answers (2)

Turophile
Turophile

Reputation: 3405

How about this small variation on your work:

SELECT id, parent_id, title, created_at 
FROM `items` 
ORDER BY COALESCE(`parent_id`, `id`), created_at

In this fiddle: http://sqlfiddle.com/#!9/d3dd87/10

Revised to order parents by timestamp and children within parents by timestamp:

SELECT id
     , parent_id
     , title
     , created_at 
     , COALESCE((SELECT created_at FROM items as parent WHERE parent.id = items.parent_id),created_at) parent_created_at
  FROM items 
 ORDER 
    BY parent_created_at
     , created_at

New fiddle: http://sqlfiddle.com/#!9/d3dd87/18

Upvotes: 2

Kevin Postlewaite
Kevin Postlewaite

Reputation: 615

I think that you want:

SELECT COALESCE(`parent_id`, `id`) as pid, title, created_at 
FROM `items` 
ORDER BY COALESCE(`parent_id`, `id`), created_at

Upvotes: 0

Related Questions