Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table groups (
       group_id varchar,
       parent_id varchar
       );

insert into groups (group_id, parent_id) values
       ('A',NULL),
       ('B','A'),
       ('C','B'),
       ('D','C'),
       ('E','A')
       ;
5 rows affected

with recursive cte as (
    select group_id, parent_id, group_id root_id, 0 lvl from groups where group_id = 'B'
    union all
    select g.group_id, g.parent_id, c.root_id, c.lvl + 1
    from groups g
    inner join cte c on c.group_id = g.parent_id
)
select * from cte order by lvl, group_id


group_id
parent_id
root_id
lvl


B
A
B
0

C
B
B
1

D
C
B
2


with recursive cte as (
	select group_id, parent_id, group_id root_id, 0 lvl from groups
	union all
	select g.group_id, g.parent_id, c.root_id, c.lvl + 1
	from groups g
	inner join cte c on c.group_id = g.parent_id
)
select group_id, parent_id, root_id from cte order by group_id, parent_id, lvl


group_id
parent_id
root_id


A
null
A

B
A
B

B
A
A

C
B
C

C
B
B

C
B
A

D
C
D

D
C
C

D
C
B

D
C
A

E
A
E

E
A
A