Jason Zhu Jason Zhu - 22 days ago 6
SQL Question

How to find leaf node in multiple branches of a tree in PostgreSQL

Here's the dataset, which represents branches of a tree. Obviously, node 3, 5 are leaf nodes.

branch_of_tree
1
1/2
1/2/3
1/2/4
1/2/4/5


I intend to find all leaf nodes, so for the above example, it should be node 3 and node 5. Could anyone give me an idea how to solve it in PostgreSQL? Thanks!

Answer

Try this query:

SELECT Tb.* FROM T as Tb
WHERE NOT EXISTS (SELECT * FROM T WHERE 
                     T.branch_of_tree LIKE Tb.branch_of_tree || '%'
                     AND T.branch_of_tree <> Tb.branch_of_tree )