Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE descriptor_value ( > id bigint NOT NULL, > value varchar(250), > parent_id bigint > ) ; > insert into descriptor_value values(1, 'foo', null), (2, 'bar', 1), (3, 'baz', 2); > > <pre> > ✓ > 3 rows affected > </pre> <!-- --> > with recursive cte as ( > select id child_id, id parent_id, 1 lvl, parent_id real_parent_id > from descriptor_value dv > where not exists ( > select 1 from descriptor_value dv1 where dv1.parent_id = dv.id > ) > union all > select c.child_id, dv.id parent_id, lvl + 1, dv.parent_id > from cte c > inner join descriptor_value dv on dv.id = c.real_parent_id > ) > select child_id, parent_id, lvl from cte order by lvl > > <pre> > child_id | parent_id | lvl > -------: | --------: | --: > 3 | 3 | 1 > 3 | 2 | 2 > 3 | 1 | 3 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f69ffde1c3fed25e1ea8d3ce6fd70e8e)*
back to fiddle