I want to count all descendant of node
with recursive t as (
select a.id, 0 cntr from a
where parent_id = 12 union all
select a.id, cntr + 1 from t
join a on a.parent_id = t.id
where cntr <= 100
)
select * from t
But this example get depth count. I want to get all descendant different levels, and limit it. Result like this:
12, 0
13, 1
17, 2
...
232, 100
Table very big, select * and count it - not an option How can i do this?
You are close. You need to retain the id as the child to join to the parent in your recursive term (you don't have to SELECT it in the end, but it needs to be present for your join). When you select, you want a count of records for each id, your counter is really more of a depth, but if your hierarchy isn't linear (like it branches out) then depth doesn't quite get what you want.
with recursive t as (
select ae.id, ae.id as child_id, 0 cntr
from a
where parent_id = 12
union all
select t.id, a.id, cntr + 1 from t
join a on a.parent_id = t.child_id
where cntr <= 100
)
select id, count(*) from t group by id
If depth or count isn't what you are after, perhaps it's just a row_number of the descendant nodes? Change that select to:
select child_id, row_number() OVER (ORDER BY Child_Id) FROM t;
