Input
record_id hierarchy_level component
1 0 a
2 1 b
3 2 c
4 3 d
5 1 e
6 2 f
7 3 g
8 3 h
9 4 i
10 2 j
Expected Output-
record_id hierarchy_level component get_parent_component
1 0 a
2 1 b a
3 2 c b
4 3 d c
5 1 e a
6 2 f e
7 3 g f
8 3 h f
9 4 i h
10 2 j e
Write a query to find the parent component for the component in the parent-child hierarchy. Desired result is stored in get_parent_component column.
Explanation - The last value of the previous hierarchy_level is the parent for the component.
I have tried the following code,
select record_id
,hierarchy_level
,component
, LAST_VALUE(component) OVER(partition by [hierarchy_level]
ORDER BY record_id RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) get_parent_component
from df1
order by record_id
I understand the partition by hierarchy level would search for the same hierarchy level, but we need to go one level up and look for the last value. Any idea on how would this be achieved?
DDL
create table tab (record_id int,hierarchy_level int,component varchar(20),get_parent_component varchar(20));
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('1','0','a','');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('2','1','b','a');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('3','2','c','b');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('4','3','d','c');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('5','1','e','a');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('6','2','f','e');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('7','3','g','f');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('8','3','h','f');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('9','4','i','h');
INSERT INTO tab (record_id,hierarchy_level,component,get_parent_component) VALUES ('10','2','j','e');