How can I generate sequence number for sql select that gives sub numbers for descendant items?

user_9304362 0
Viewed 60

I would like to generate sequence numbers for select that gives sub numbers for descendant items.

I want the numbers be the following format:

  • root: 1...n
  • children of root: 1.1 -> 1.n
  • sub children: 1.1.1 -> 1.1.n
  • and so on...

I have Item table which has an owner_ref foreign key the table: (name of items is just an example, it can be anything)

 id | item_name  | parent_id | owner_ref_id 
----|------------|-----------|--------------
 1  | item_1     | null      |  1
 2  | item_1.1   | 1         |  1
 3  | item_1.1.1 | 2         |  1
 4  | item_2     | null      |  1
 5  | item_2.1   | 4         |  1
 6  | item_2.2   | 4         |  1
--------------------------------------------

The outcome should looks like :


 seq_num | item_name  | parent_id | owner_ref_id 
---------|------------|-----------|--------------
 1       | item_1     | null      |  1
 1.1     | item_1.1   | 1         |  1
 1.1.1   | item_1.1.1 | 2         |  1
 2       | item_2     | null      |  1
 2.1     | item_2.1   | 4         |  1
 2.2     | item_2.2   | 4         |  1
--------------------------------------------
postgresql sql
1 Answers

Use recursive CTE to form a tree-like structure -

with recursive nodes(id,item_name, parent_id,lvl, path) as (
    select id,item_name, parent_id, 1
  , row_number() OVER (order by parent_id nulls first)::text as path
    from items where parent_id is null
union all
    select o.id,o.item_name, o.parent_id,n.lvl+1, n.path || '.' || 
  row_number() OVER (partition by o.parent_id order by o.parent_id)::text
    from items o
    join nodes n on n.id = o.parent_id
)
select *
from nodes
order by id

View on DBFiddle

user_92392670
Related