> Could I ask how you have implemented the recursion - i.e. in the
> client, using a CTE or with a stored procedure?

In this particular case I am not using a recursion in SQL.

I have another scenario where sales items are grouped and the groups'
hierarchy can be changed dynamically by many concurrent users, and
there I have used stored procedure in FB to find all items
belonging to all n levels of subgroups of a certain group.

Anyway I do favour fetching only those records the user currently
can process, or is effectively looking at. Just received Helen's
new book and the first page I opened (464 in Vol. 2) says
something about "who can process 200.000 rows at once anyway?".
Exactly what I am trying to tell all my old dBase companions, but now
that Helen writes it they'll probably start believing me :-)

I am using nested "foreach..." loops in C#/ADO.net to open all
sub-items of any given parent item. I am sure that Delphi/C++ et al
will have the same options.
This virtually means I have one query sent to the FB server for each
hierarchy level, constrained to paragraphs having the current
paragraph as parent (WHERE IDParent = :IDThisParagraph...).

The TreeItems/Nodes have a custom property "IDParagraph", and so in
the adjacent "Details" screen I can load all details for one
paragraph, move it up and down the hierarchy, delete, modify it, add
new paragraphs etc.
I keep a local storage for details already fetched from the database,
so they won't be fetched again unless they have been changed by
another user.

I don't exactly know, by the way, what a CTE is.
Something similar as a VIEW in Firebird, I guess. If you need to
implement as much as possible in SQL, it is probably a good idea to
check Global Temp Tables in FB 2.x upwards.

> The reason for asking is that AFAIK the ordering in the result set
> (from a CTE at least) is not defined. Therefore you could end up with: