How to get the top most parent in PostgreSQL

Question

I have a tree structure table with columns: id,parent,name. Given a tree A->B->C, how could i get the most top parent A's ID according to C's ID? Especially how to write SQL with "with recursive"? Thanks!

Answer 1

To implement recursive queries, you need a Common Table Expression (CTE). This query computes ancestors of all parent nodes. Since we want just the top level, we select where level=0.

WITH RECURSIVE Ancestors AS
(
   SELECT id, parent, 0 AS level FROM YourTable WHERE parent IS NULL
   UNION ALL
   SELECT child.id, child.parent, level+1 FROM YourTable child INNER JOIN
      Ancestors p ON p.id=child.parent

)
SELECT * FROM Ancestors WHERE a.level=0 AND a.id=C

If you want to fetch all your data, then use an inner join on the id, e.g.

SELECT YourTable.* FROM Ancestors a WHERE a.level=0 AND a.id=C
   INNER JOIN YourTable ON YourTable.id = a.id

Answer 2

WITH    RECURSIVE q AS
        (
        SELECT  m
        FROM    mytable m
        WHERE   id = 'C'
        UNION ALL
        SELECT  m
        FROM    q
        JOIN    mytable m
        ON      m.id = q.parent
        )
SELECT  (m).*
FROM    q
WHERE   (m).parent IS NULL