CODEWITHSUNDEEP
sqlpostgresql
KAs
KAs

Reputation: 1868

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!

Upvotes: 2

Views: 1026

Answers (2)

valex
valex

Reputation: 24144

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 )

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246568

Maybe something like the following:

SELECT t.branch_of_tree
FROM tree t
WHERE NOT EXISTS
        (SELECT 1
         FROM tree t2
         WHERE t.branch_of_tree <> t2.branch_of_tree
           AND position(t.branch_of_tree in t2.branch_of_tree) = 1);

┌────────────────┐
│ branch_of_tree │
├────────────────┤
│ 1/2/3          │
│ 1/2/4/5        │
└────────────────┘
(2 rows)

Upvotes: 1

Related Questions