1

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:

enter image description here

I just want rows 24 and 27 to be selected.

1
  • 26 has no children either. Why isn't selected? May 5, 2017 at 21:10

2 Answers 2

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.)

1
  • You beat me to the solution. Mine was (ALMOST) identical.
    – Wes
    May 5, 2017 at 22:12
1

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 |
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.