Sasha Matijasic escreveu:

>> is there any way how to limit recursion when using CTE. Something like
>> select * from MyCTE
>> OPTION (MAXRECURSION 3)
>>
>> that's in MSSQL.
>>
>
> Hi,
> this is probably not what you are asking for and you might already be aware of it, but you could do something like this
>
> with cte as(
> select ..., 0 lvl
> from ... a
> union all
> select ..., a.lvl + 1
> from ...
> join cte...
> )
> select * from cte
> where lvl < :max_level
>
> Of course it's slower and more difficult to write than having it as an option of cte, but at least it works...
>

It should be better to limit the level inside the CTE, I think:

with recursive x as (
select id, mae, 1 levelx
from conta
where mae is null
union all
select conta.id, conta.mae, x.levelx + 1
from conta
join x
on conta.mae = x.id
where x.levelx + 1 <= 2
)
select *
from x;


Adriano