CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Holden
Holden

Reputation: 333

How to SQL query through a row hierarchy?

Suppose I have the following table structure, in an Oracle database, where PARENT references another row in the same table.

  id  | parent
  1   | null
  2   | 1
  3   | 2
  4   | 3
  5   | null
  6   | 5
  7   | 6

How could I query so that each row gets me the end of the sequence. That is I'd like to see this end result:

  id  | end_parent
  1   | 4
  2   | 4
  3   | 4
  4   | 4
  5   | 7
  6   | 7
  7   | 7

Upvotes: 0

Views: 89

Answers (2)

bruceskyaus
bruceskyaus

Reputation: 783

Use connect by syntax, and create a path using sys_connect_by_path. The last value of the path is the end_parent value. Use connect_by_root to get the id of the root parent. Based on this group, get the maximum value as the end_parent for each group. Finally, join the grouping to each row.

with id_paths as
(
    select
    t.id,
    substr(sys_connect_by_path(t.id,'>'),2,length(sys_connect_by_path(t.id,'>'))-1) as id_path,
    connect_by_root t.id as parent_group
    from my_table t
    connect by prior t.id = t.parent
    start with t.parent is null
),
end_parents as
(
    select 
    ip.parent_group,
    max(substr(ip.id_path, instr(ip.id_path,'>',-1)+1, length(ip.id_path))) as end_parent
    from id_paths ip
    group by ip.parent_group
)
select
ip.id,
ep.end_parent
from id_paths ip
inner join end_parents ep on ep.parent_group = ip.parent_group
order by ip.id;

Output

| ID | END_PARENT |
|----|------------|
|  1 |          4 |
|  2 |          4 |
|  3 |          4 |
|  4 |          4 |
|  5 |          7 |
|  6 |          7 |
|  7 |          7 |

SQL Fiddle Example

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

The following query shows the result you want, tested on Oracle 12c:

with
x (id, parent, origin, generation) as (
  select id, parent, id, 1 from my_table where parent is null
  union all
  select t.id, t.parent, x.origin, x.generation + 1
   from my_table t
  join x on t.parent = x.id
),
y (id, parent, generation, origin, rn) as (
  select id, parent, generation, origin, 
    row_number() over(partition by origin order by generation desc) as rn
  from x
),
z (id, origin) as (
  select id, origin from y where rn = 1
)
select x.id, z.id as end_parent
from x
join z on x.origin = z.origin
order by x.id

Result:

ID  END_PARENT
--  ----------
1   4
2   4
3   4
4   4
5   7
6   7
7   7

For reference, here's the data I used:

create table my_table (
  id int,
  parent int
);

insert into my_table (id, parent) values (1, null);
insert into my_table (id, parent) values (2, 1);
insert into my_table (id, parent) values (3, 2);
insert into my_table (id, parent) values (4, 3);
insert into my_table (id, parent) values (5, null);
insert into my_table (id, parent) values (6, 5);
insert into my_table (id, parent) values (7, 6);

Upvotes: 2

Related Questions