By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table myTable
(
ID int Primary key,
Detail_ID int references myTable(ID) null, -- reference to self
Master_Value varchar(50) -- references to master table
)
insert into myTable select 100,null,'aaaa'
insert into myTable select 101,100,'aaaa'
insert into myTable select 102,101,'aaaa'
insert into myTable select 103,102,'aaaa' ---> last record
insert into myTable select 200,null,'bbbb'
insert into myTable select 201,200,'bbbb'
insert into myTable select 202,201,'bbbb' ---> last record
7 rows affected
select * from mytable
| ID | Detail_ID | Master_Value |
|---|---|---|
| 100 | null | aaaa |
| 101 | 100 | aaaa |
| 102 | 101 | aaaa |
| 103 | 102 | aaaa |
| 200 | null | bbbb |
| 201 | 200 | bbbb |
| 202 | 201 | bbbb |
with cte as (
select master_value, id as first_id, id as child_id, convert(varchar(max), id) as path, 1 as lev
from mytable t
where detail_id is null
union all
select cte.master_value, cte.first_id, t.id, concat(path, '->', t.id), lev + 1
from cte join
mytable t
on t.detail_id = cte.child_id and t.master_value = cte.master_value
)
select cte.*
from (select cte.*, max(lev) over (partition by master_value) as max_lev
from cte
) cte
where max_lev = lev
| master_value | first_id | child_id | path | lev | max_lev |
|---|---|---|---|---|---|
| aaaa | 100 | 103 | 100->101->102->103 | 4 | 4 |
| bbbb | 200 | 202 | 200->201->202 | 3 | 3 |