Search code examples
sqloracle-databasehierarchical

Oracle SQL hierarchical query from bottom to top element

In my table I store the successor of each entry.

+----+-----------+--+
| ID | SUCCESSOR |  |
+----+-----------+--+
|  1 |         2 |  |
|  2 |         3 |  |
|  3 |           |  |
+----+-----------+--+

I need to get from ID 3 to ID 1.

I have tried to archieve this with the following query, but this does not work. :-(

SELECT NVL (id, 3)
FROM my_table
WHERE LEVEL = 1
CONNECT BY id = PRIOR successor
START WITH id = 3;

Can somebody please give me some advice how to get this working?

Solution

  • The following version should also provide the correct answer:

    with my_table as 
(select 1 id, 2 successor from dual union
select 2 id, 3 successor from dual union
select 3 id, null successor from dual )
SELECT id FROM my_table
WHERE level = 3
CONNECT BY successor = PRIOR id 
START WITH successor is null
;