Retrieve ascendant rows of a hierarchy

Question

I have a sample table that contains 3 columns: id, parent_id and name

Let's say that the hierarchy that is present in my table is as follows:

I
  I.A
    I.A.a
    I.A.b
      I.A.b1
      I.A.b2
  I.B
  I.C
II
  II.A

My goal is to retrieve all the ascendants of a given row. For example, for I.A.b2, the result would be:

I
I.A
I.A.b
I.A.b2

I tried to use the following connect-by query but with no success:

 SELECT name
 FROM test 
 where id = 7 -- 7 is the id of I.A.b2 in my example
 CONNECT BY PRIOR id = parent_id
 START WITH parent_id is null;

Any suggestion would be helpful

I'm assuming this is Oracle. Please tag it as such to help folks find this question that follow that tag. — JNevill, Jan 15 '18 at 15:37

trincot · Accepted Answer · 2018-01-16T11:53:18.977

3

The trick is to go through the records in the opposite order (start at the end) and then reverse the order with an order by as follows:

SELECT     name
FROM       test 
CONNECT BY PRIOR parent_id = id
START WITH id = 7
ORDER BY   rownum DESC

To get the level of each record in the tree, you could use the row_number() analytic function:

SELECT     ROW_NUMBER() OVER (ORDER BY rownum DESC), name
FROM       test 
CONNECT BY PRIOR parent_id = id
START WITH id = 7
ORDER BY   rownum DESC

edited Jan 16 '18 at 11:53

answered Jan 15 '18 at 15:41

trincot

The query works perfectly, but I have an additional feature to retrieve the level of the hierarchy. Reversing the order causes also the level to be reversed. Is there a way to get the original level? Thanks – Strider Jan 16 '18 at 11:39
1

See addition to my answer. – trincot Jan 16 '18 at 11:53

1 Answers1