clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591665 fiddles created (45713 in the last week).

CREATE TABLE descriptor_value ( id bigint NOT NULL, value varchar(250), parent_id bigint ) ;
 hidden batch(es)


insert into descriptor_value select 1, 'foo', null union all select 2, 'bar', 1 union all select 3, 'baz', 2
3 rows affected
 hidden batch(es)


with recursive cte as ( select dv.id, dv.value, dv.parent_id, dv.parent_id as root, 1 as lev, dv.id::text as path from descriptor_value dv where dv.parent_id is null union all select dv.id, dv.value, dv.parent_id, cte.root, lev + 1, cte.path || '-->' || (dv.id::text) from cte join descriptor_value dv on dv.parent_id = cte.id ) select cte.id, cte.value, cte.parent_id, cte.root, lev, max(lev) over (partition by cte.root) - lev + 1 as level from cte order by root, lev;
id value parent_id root lev level
1 foo 1 3
2 bar 1 2 2
3 baz 2 3 1
 hidden batch(es)