What about something like:
with recursive cte as
(select id, id_parent, green, SortText, SortText SortColumn
from MyTable
where Green = 'Yes'
union all
select T2.id, T2.id_parent, T2.green, T2.SortText, cte.SortColumn ||
t2.SortText
from MyTable T2
join cte on T2.ID <http://t2.id/> = cte.id_parent)
select id, id_parent, green, SortText
from cte
group by 1, 2, 3, 4
order by min(SortColumn)
Don't know whether or not it works, I use recursive CTEs very rarely and
haven't tried much sorting of them.
HTH,
Set
2017-11-06 12:32 GMT+01:00 [email protected]
[firebird-support] <[email protected]>:
>
>
> Hello,
>
>
> I have a table which data is representing a tree:
>
>
> CREATE TABLE MyTABLE (
> ID INTEGER NOT NULL,
> ID_PARENT INTEGER,
> GREEN VARCHAR(3),
> SortText VARCHAR(5),
> CONSTRAINT PK_MYTABLE PRIMARY KEY (ID));
>
> This is the data in this table:
>
> ID
>
> PARENT_ID
>
> GREEN
>
> SortText
>
> 1
>
>
>
> No
>
> A3
>
> 2
>
> 1
>
> Yes
>
> B1
>
> 3
>
> 2
>
> No
>
> C6
>
> 4
>
> 2
>
> Yes
>
> C5
>
> 5
>
> 2
>
> Yes
>
> C4
>
> 6
>
> 1
>
> No
>
> B2
>
> 7
>
> 6
>
> No
>
> C4
>
> 8
>
> 6
>
> Yes
>
> C3
>
> 9
>
>
>
> No
>
> A2
>
> 10
>
> 9
>
> No
>
> B3
>
> 11
>
> 10
>
> No
>
> C2
>
> 12
>
>
>
> No
>
> A1
>
> 13
>
> 12
>
> Yes
>
> B4
>
> 14
>
> 13
>
> No
>
> C1
>
>
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL,
> 'No', 'A3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes',
> 'B1');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No',
> 'C7');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes',
> 'C6');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes',
> 'C5');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No',
> 'B2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No',
> 'C4');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes',
> 'C3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL,
> 'No', 'A2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No',
> 'B3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No',
> 'C2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL,
> 'No', 'A1');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12,
> 'Yes', 'B4');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No',
> 'C1');
>
>
>
> Table-Data shown as Tree
>
> A3
>
>
>
>
>
>
>
> B1
>
>
>
>
>
>
>
> C7
>
>
>
>
>
> C6
>
>
>
>
>
> C5
>
>
>
> B2
>
>
>
>
>
>
>
> C4
>
>
>
>
>
> C3
>
> A2
>
>
>
>
>
>
>
> B3
>
>
>
>
>
>
>
> C2
>
> A1
>
>
>
>
>
>
>
> B4
>
>
>
>
>
>
>
> C1
>
> Now I need a dataset which suspends the green tree-nodes with the
> complete tree-path for each green cell. This dataset should be ordered
> alphabetically (A1 before A3 and C5 before C6)
>
> A1
>
>
>
>
>
>
>
> B4
>
>
>
> A3
>
>
>
>
>
>
>
> B1
>
>
>
>
>
>
>
> C5
>
>
>
>
>
> C6
>
>
>
> B2
>
>
>
>
>
>
>
> C3
>
>
>
>
> *I (almost) get the result I want with this statement: *
> with recursive cte as
>
> (select id, id_parent, green, SortText
>
> from MyTable
>
> where Green = 'Yes'
>
> union all
>
> select T2.id, T2.id_parent, T2.green, T2.SortText
>
> from MyTable T2
>
> join cte on T2.ID = cte.id_parent)
>
>
>
> select distinct id, id_parent, green, SortText
>
> from cte
>
>
>
>
>
>
>
> The dataset is ok, but the ordering is not (because there is no “Depth
> First by” – Clause)
>
>
>
> With FB3 I tried to use windows functions (something like below):
>
> Rank() over(Partition by id_parent order by SortText)
>
>
>
> But I could not solve my problem! - For instance, I got the message:
> Recursive member of CTE cannot use aggregate or window function.
>
>
> Could anybody give me a hint how to solve this.
>
>
> Thank you,
>
> Josef
>
>
>
>
>
>
