How to get the first element from a linked list in Oracle?

Question

I have a table in my Oracle-DB that looks like this:

╔════╦════════════════╦═════════════╗
║ ID ║ Predecessor_id ║ Information ║
╠════╬════════════════╬═════════════╣
║ 1  ║     NULL       ║    foo      ║
║ 2  ║     1          ║    bar      ║
║ 3  ║     2          ║    muh      ║
║ 4  ║     NULL       ║    what     ║
║ 5  ║     4          ║    ever     ║
╚════╩════════════════╩═════════════╝

I need a SELECT that returns something like this:

╔════╦════════════════╦════════════════════╦═════════════╗
║ ID ║ Predecessor_id ║ First_list_element ║ Information ║
╠════╬════════════════╬════════════════════╬═════════════╣
║ 1  ║     NULL       ║         1          ║    foo      ║
║ 2  ║     1          ║         1          ║    bar      ║
║ 3  ║     2          ║         1          ║    muh      ║
║ 4  ║     NULL       ║         4          ║    what     ║
║ 5  ║     4          ║         4          ║    ever     ║
╚════╩════════════════╩════════════════════╩═════════════╝

The table has some kind of linked list aspect. The first element of the List is the one with no predecessor. What I need is the information for every row in which List it is a member of. The list is defined by the ID of the first element.

In a programming language I would implement some kind of lookup table. But in SQL I have no idea. I would prefer SQL but if only PL/SQL gives me a response I'll take that as well.

Answer 1

You should use CONNECT_BY_ROOT operator

When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators004.htm#i1035022

http://sqlfiddle.com/#!4/121f02/1

create table  tbl(
id number,
Predecessor_id number,
Information varchar2(250));


insert into tbl values(1 ,NULL          ,'foo'    );
insert into tbl values(2 ,1             ,'bar'    );
insert into tbl values(3 ,2             ,'muh'    );
insert into tbl values(4 ,NULL          ,'what'   );
insert into tbl values(5 ,4             ,'ever'   );

SELECT tbl.*, connect_by_root id first_list_element
  FROM tbl
CONNECT BY PRIOR id = predecessor_id
 START WITH predecessor_id IS NULL