Hi all,
i'm trying to combine the new Window functions in Fb3 with
recursive CTEs to get a _defined_ ordering of the recursive
child records in the result set.
Since we don't have "DEPTH FIRST BY <ordering_columns>" or
"BREADTH FIRST BY <ordering_columns>" clauses in recursive CTEs
yet, i'm trying to work around this by using the new RANK()
function. (honestly, i didn't even know those DEPTH/BREADTH
clauses exist in the SQL "standard" - thanks, Vlad! ;-) )
A simple example:
CREATE TABLE T1 (ID INTEGER,ID_PARENT INTEGER,NAME CHAR(32));
INSERT INTO T1 (ID, ID_PARENT, NAME) VALUES (0, -1, 'Parent');
INSERT INTO T1 (ID, ID_PARENT, NAME) VALUES (1, 0, 'Child2');
INSERT INTO T1 (ID, ID_PARENT, NAME) VALUES (2, 0, 'Child1');
The target: a recursive CTE that will return
Parent - Child1 - Child2
and not
Parent - Child2 - Child1 (or anything else, in different order).
The following CTE would get me started, i thought...
with recursive rcte as(
select id,id_parent,name,0 as lvl, 0 as rnk
from t1 where id_parent<0
union all
select ch.id,ch.id_parent,ch.name,pa.lvl+1,
rank() over(partition by ch.id_parent order by ch.name)
from t1 ch join rcte pa on (ch.id_parent=pa.id))
select * from rcte
...but it leads to an endless loop/recursion (using latest Fb3 beta)
:-(
Options:
a) [ ] file it as a bug - this _should_ work!
b) [ ] window functions are not supposed to work in the "child select"
part of recursive CTEs (they work fine in the "anchor" query, though
- but that only gets executed once...)
c) [ ] wait until "DEPTH|BREADTH FIRST BY <col>" is implemented
d) [ ] you're doing it all wrong, try this instead: (...)
e) [ ] (...)
Your opinion, a) .. e) ?
(i'm VERY interested in the "(...)" part of option d) as well as
everything in option e) :-) )
cheers,
Frank
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
