How to use ROWNUM in a recursive CTE?-CodePudding

I have the following recursive CTE that starts from a row (a tree node) and goes up through ancestors to the root

with ancestor_seq (
    pre, parent
) as (
    select pre, parent
    from form_data_stage
    where pre = 16
    union all
    select d.pre, d.parent
    from ancestor_seq a 
    inner join form_data_stage d on d.pre = a.parent
)
select * from ancestor_seq;

It returns the expected result

I want to get the 4th row only. If I try the same query but with there ROWNUM condition at the end -

with ancestor_seq (
    pre, parent
) as (
    select pre, parent
    from form_data_stage
    where pre = 16
    union all
    select d.pre, d.parent
    from ancestor_seq a 
    inner join form_data_stage d on d.pre = a.parent
)
select * from ancestor_seq WHERE ROWNUM = 4;

I get an empty result.

I can add a level, e.g.

with ancestor_seq (
    pre, parent, lvl
) as (
    select pre, parent, 1 lvl
    from form_data_stage
    where pre = 16
    union all
    select d.pre, d.parent, a.lvl   1
    from ancestor_seq a 
    inner join form_data_stage d on d.pre = a.parent and a.lvl <= 4
)
select pre, parent from ancestor_seq where lvl = 4;

this works -

pre parent
10  9

but it is checking the level value twice. curious if there is a better way, and why rownum does not appear to work.

CodePudding user response：

why rownum does not appear to work.

This is almost the same case as my answer to this quesiton.

It is not working because: for the first row ROWNUM is 1 and since your WHERE clause is ROWNUM=4 then this reduces to 1=4 and the row is discarded. The subsequent row will then be tested against a ROWNUM of 1 (since the previous row is no longer in the output and will not have a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail the WHERE filter and are discarded.

If you want to get ROWNUM to work then you need to generate it in an inner query and filter in an outer query:

with ancestor_seq (
    pre, parent
) as (
    select pre, parent
    from form_data_stage
    where pre = 16
    union all
    select d.pre, d.parent
    from ancestor_seq a 
    inner join form_data_stage d on d.pre = a.parent
)
SELECT *
FROM   (
  select a.*,
         ROWNUM AS rn
  from   ancestor_seq a
)
WHERE  rn = 4;