Self-Relationship query with oracle (children doesn't have the same father)

Question

I'm trying to work on a query involving a self-relationship table.

I have these rows:

ID    Parent_ID

1     null
2     1
3     2
4     3
.
.
.

The "children" doesn't share the same father. Each 'father' only have one child. I have the ID of the last 'child'. (For example, i have the ID = 4)..I'd like to get this:

  1     null
  2     1
  3     2
  4     3

How can i retrieve these rows, given that the Parent Id may not be in a sequential order.

Thanks in advance.

Answer 1

Another option is to use a recursive cte:

with cte (id, parent_id) as (
  select id, parent_id
  from yourtable
  where id = 4
  union all
  select y.id, y.parent_id
  from yourtable y
    join cte c on y.id = c.parent_id
  )
select * from cte order by id

SQL Fiddle Demo

Answer 2

Oracle supports recursive queries:

SELECT t.id, t.parent_id
FROM t 
START WITH t.id = 4
CONNECT BY PRIOR t.parent_id = t.id

sqlfiddle here.