find rows with no child and if has child latest child

Question

I have a simple table like this:

id
parent_id
created_at

with parent/child relationship.
If a row is child, then it has a parent_id, else its parent_id is 0.

Now I want to select all rows that either have no child (so itself) or have a child, so get the latest child by created_at (and not include itself in the final results).

As a visual example you can look at this tiny picture:

I just want rows 24 and 27 to be selected.

Answer 1

Is id guaranteed to be increasing along with created_at? In other words, can I just do a MAX(id) to always find the latest child value for the same parent_id? Or do I actually need to use the created_at date?

If I don't have to actually use the date, then you can do:

--rows that are not parent or child
SELECT id, parent_id
FROM mytable
WHERE parent_id = 0                              --not a child
AND id NOT IN (SELECT parent_id FROM mytable)    --not a parent

UNION ALL

--latest child of parent
SELECT MAX(id) as id, parent_id
FROM mytable
WHERE parent_id <> 0
GROUP BY parent_id

Gets kinda funny with the GROUP BY if you are wanting to include a bunch of other columns from the table.

But either way, a good general strategy is handling the two parts separately and use UNION ALL TO mash the results together. (UNION will check the two sets for duplicates, so a UNION ALL is preferred for performance reasons, since we know there won't be an overlap here.)

Answer 2

I've added a few more rows just to cover all cases.

drop table if exists mytable;
create table if not exists mytable
( 
    id int,
    parent_id int,
    created_at date
);

insert into mytable values
(24,  0, '20170101'),
(25,  0, '20170101'),
(26 ,25, '20170101'),
(27, 25, '20170102'),
(28,  0, '20170102'),
(29, 28, '20170102'),
(30, 28, '20170103'),
(31, 20, '20170103');  


select id, parent_id
from   mytable
where  (parent_id = 0 and not exists (select 1
                                      from   mytable m2
                                      where  m2.parent_id = mytable.id))
       or
       (id = (select     m3.id
              from       mytable m3
              inner join mytable m4
              on         m4.parent_id = 0
              where      m4.id = mytable.parent_id
              and        m3.parent_id = m4.id
              order by   m3.created_at desc
              limit 1))
;

This is the result:

| id | parent_id |
|----|-----------|
| 24 | 0         |
| 27 | 25        |
| 30 | 28        |

Rextester here

First condition select all rows where parent_id=0 and not exists any children:

where  (parent_id = 0 and not exists (select 1
                                      from   mytable m2
                                      where  m2.parent_id = mytable.id))

Second condition select all rows where its parent is at first level. parent_id=0 ordered by created_at desc. (Limit 1 on main query)

select     m3.id, m3.parent_id, m3.created_at
from       mytable m3
inner join mytable m4
on         m4.parent_id = 0
and        m3.parent_id = m4.id
order by   m3.created_at desc;

This returns this rows:

| id | parent_id | created_at          |
|----|-----------|---------------------|
| 30 | 28        | 03.01.2017 00:00:00 |
| 27 | 25        | 02.01.2017 00:00:00 |
| 29 | 28        | 02.01.2017 00:00:00 |
| 26 | 25        | 01.01.2017 00:00:00 |