find rows with no child and if has child latest child 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.
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 &lt;&gt; 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.)