Sreenivasa Reddy Thotli

WITH r ( id , parent , ultimate_parent , name , ultimate_parent_name , lvl ) as ( SELECT id , parent_id AS parent , parent_id AS ultimate_parent , name , name as ultimate_parent_name , 0 lvl FROM mytable WHERE parent_id = id -- identifies a root UNION ALL SELECT m . id , r . id , ultimate_parent , m . name , r . ultimate_parent_name , r . lvl + 1 FROM r join mytable m on m . parent_id = r . id -- joins child with parent WHERE m . parent_id <> m . id -- to avoid cycles ) SELECT * FROM r ;   The first part of the subquery fetches the roots and the second part connects  the children.   Parent is the immediate parent and ultimate_parent , the ultimate parent.