Hi,
what about this?
with recursive cte as
(select M.id, M.id_parent, M.green, CAST(M.SortText AS VARCHAR(100)) AS
SortText
from MyTable M
where M.Green = 'Yes'
union all
select T2.id, T2.id_parent, T2.green, T2.SortText || '->' || C.SortText
from MyTable T2
join cte C on T2.ID = C.id_parent)
select distinct id, id_parent, green, SortText
from cte
order by SortText
regards,
Karol Bieniaszewski
From: josef.gschwendt...@quattro-soft.de [firebird-support]
Sent: Monday, November 6, 2017 12:32 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Sorting-Problem on recursive query (window
functions)
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
