MySQL – How to Find Rows with No Child and Latest Child

MySQL

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.

Best Answer

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

Related Question