Hi Maxim,

please check this recursion output step by step.

Level =1

p   c   level	rn	
-   -   -----   --  
1   0   1	     1

Level =2

p   c   level	rn	
-   -   -----   --  
1   0   1	     1
2   1   2	     1
3   1   2	     2
4   1   2	     3

Level =3 for (p=2,3,4)

Level =3 and (p=2)

p   c   level	rn	
-   -   -----   --  
1   0   1	     1
2   1   2	     1
3   1   2	     2
4   1   2	     3
5   2   3	     1
6   2   3	     2

Level =3 and (p=3)

p   c   level	rn	
-   -   -----   --  
1   0   1	     1
2   1   2	     1
3   1   2	     2
4   1   2	     3
5   2   3	     1
6   2   3	     2
7   3   3	     1
8   3   3	     2

Level =3 and ((p=4) and (p=5) ) Here the same output as the previous becuase the maximun value of column c is 3 so

inner join cte c2 on c2.p = c.c getting null where (p = 4 and p=5)

now at the end you are doing order by level,rn. so your output is OK.

I think that ROW_NUMBER should be working like in example

if OBJECT_ID('tempdb.dbo.#lt') is not null 
    drop table #lt

create table #lt (p int, c int)

insert into #lt
values
(1,0),
(2,1),
(3,1),
(4,1),
(5,2),
(6,2),
(7,3),
(8,3);

with cte as 
(
    select *, level = 1, rn = ROW_NUMBER() over (order by p) from #lt where c in (0)
    union all
    select *,
    rn = ROW_NUMBER() over (partition by level order by p)
    from (
        select c.*,
        level = c2.level + 1
        from #lt c
        inner join cte c2
        	on c2.p = c.c
    )as t
)
select 
    *,
    rn_expected = ROW_NUMBER() over (partition by level order by p)
from cte
order by level,p

p   c	level	rn	rn_expected
-   -	------	--	-----------------
1   0	1	1	1
2   1	2	1	1
3   1	2	2	2
4   1	2	3	3
5   2	3	1	1
6   2	3	2	2
7   3	3	1	3
8   3	3	2	4

write this query

with cte as 
(
    select *, level = 1  from #lt where c in (0)
    union all
        select c.*,
        level = c2.level + 1
        from #lt c
        inner join cte c2
        	on c2.p = c.c
)
select *,ROW_NUMBER() OVER ( PARTITION BY level ORDER BY p) AS rn
from cte

I need to number the rows at the level of recursion. Depends on it when I need to get out of the recursion.

I think its not possible in recursion because when your P value is 2 then recursion will get two rows so it will generate 1 and 2 as Row Number .
Next recursion when your P value is 3 then recursion will get two rows so it will generate 1 and 2 Row number same as previous.
ROW_NUMBER() function works on Number of rows return by query.

If your problem is solved, please mark the question as solved.