How to get the top most parent in PostgreSQL

I have a tree structure table with columns: id,parent,name. Give开发者_如何转开发n 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!

---

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

---

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

---

Assuming a table named "organization" with properties id, name, and parent_organization_id, here is what worked for me to get a list that included top level and parent level org ID's for each level.

WITH RECURSIVE orgs AS (
    SELECT
        o.id as top_org_id
        ,null::bigint as parent_org_id
        ,o.id as org_id
        ,o.name
        ,0 AS relative_depth
    FROM organization o
    UNION
        SELECT
            allorgs.top_org_id
            ,childorg.parent_organization_id
            ,childorg.id
            ,childorg.name
            ,allorgs.relative_depth + 1
        FROM organization childorg
        INNER JOIN orgs allorgs ON allorgs.org_id = childorg.parent_organization_id
) SELECT
    *
FROM
    orgs order by 1,5;