CODEWITHSUNDEEP
sqloracle-database
bawpie
bawpie

Reputation: 477

Oracle SQL - Find original ID in a chain

I have some data that looks like this:

ACT_ID  ACT_ID_FROM
1A  
1B       1A
1C       1B
2A  
2B       2A
2C       2B

However, I'd like to be able to create a third column for the data so it looks like this:

     ACT_ID ACT_ID_FROM ORIG_ID
1A                   1A
1B      1A           1A
1C      1B           1A
2A                   2A
2B      2A           2A
2C      2B           2A

Basically, I'm interested in the originating ID of each line, so 1C and 1B both originally came from ACT_ID 1A (which can be determined from the ACT_ID_FROM column), but I'm not sure how to get the 1A out, particularly when there have been a number of IDs in a chain. I'm using oracle 10g.

A similar question has already been asked here: Find last record in a single-table chain (SQL Server) but I'm not sure how to apply this to my own problem.

Thanks in advance for any advice or assistance.

Upvotes: 0

Views: 291

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

It can be achieved by selecting rows in hierarchical order using start with and connect by clauses, and connect_by_root() operator:

SQL> with t1(act_id, act_id_from) as(
  2    select '1A', null from dual union all
  3    select '1B', '1A' from dual union all
  4    select '1C', '1B' from dual union all
  5    select '2A', null from dual union all
  6    select '2B', '2A' from dual union all
  7    select '2C', '2B' from dual
  8  )
  9  select act_id
 10       , act_id_from
 11       , connect_by_root(act_id) as orig_id
 12    from t1
 13   start with act_id_from is null
 14  connect by prior act_id = act_id_from
 15  /

Result:

ACT_ID ACT_ID_FROM ORIG_ID
------ ----------- ---------
1A                 1A
1B     1A          1A
1C     1B          1A
2A                 2A
2B     2A          2A
2C     2B          2A

6 rows selected

Upvotes: 3

Related Questions