> 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:
