Search code examples
sqloracle-databasehierarchical

oracle sql query hierarchicle query to find top level

I have an oracle table that contains and ID column, and ID_child column, and several fact fields. I know an ID and I want to find its top level parent. The id I know would but put into the ID_child field and the ID would be its parent. This query worked but I only get one row which I could do with a regular sql query. How do I get the top ID?

SELECT
item_tbl.id, item_tbl.id_child
FROM item_tbl
START WITH item_tbl.id_child = 12510646
CONNECT BY PRIOR item_tbl.id_child = item_tbl.id

results I get:

ID               ID_CHILD
12512162         12510646
Solution

  • For example: Order of the connect by matters; parent needs to be on the left side.

    With item_tbl (ID, ID_CHILD) as 
(SELECT 1,2 from dual UNION ALL
SELECT 2,3 from dual UNION ALL
SELECT 3,4 from dual)
SELECT
item_tbl.id, item_tbl.id_child, level
FROM item_tbl
START WITH item_tbl.id_child = 4
CONNECT BY PRIOR item_tbl.id= item_tbl.id_child
Order by level desc;