declare @t table
(id char(1) primary key,
parent char(1));

insert @t values 
('A',null),                                   --Level 1
('B', 'A'), ('C', 'A'),                       --Level 2
('D', 'B'), ('E', 'B'),('R','B'), ('F', 'C'), --Level 3
('G', 'D'),  --Level 4
('H', 'G'), ('I', 'G');                       --Level 5

;with cte as
(
select id, rnk=0 from @t
where parent = 'A'
 
union all
 
select t.id, rnk+1
from cte join @t t
on cte.id = t.parent
)
select *
  from cte
 where not exists
       (select *
          from @t
         where parent = cte.id);

;with cte as
(
select id, rnk=0 from @t
where parent = 'A'

union all

select t.id, rnk+1
from cte join @t t
on cte.id = t.parent
)
select *
from
(
   select *, dense_rank() over(order by rnk desc) rk
     from cte
)t
where rk = 1