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
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
Hi,
thank you for your ideas.
But I think your solution would only work if the anchor query would select
treemembers of level 1.
Whereas my query starts with "where Green = 'Yes'" and these elements are in
different levels.
I have read that this should be possible with "window functions", but
07.11.2017 08:53
(GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support]
Sorting-Problem on recursive query (window functions)
Hi,
thank you for your ideas.
But I think your solution would only work if the anchor query would select
t
Hi again, Josef! I like SQL puzzles, and decided to spend a bit of time
this afternoon trying to solve yours. Not using Fb 3, my knowledge of
windowing functions is too limited to offer any such answer, but I found
something that seems to get the result you want in Fb 2.5.
Your main problem is
Hi Set,
this is a very interesting solution.
I changed it a bit to create a "SortOrder" that should always work.
What do you think?
I will test it on a real (big) table and see how the performance is.
Maybe " cast(... as varchar(200))" is not necessary if I don't select
"cte2.SortOrder".
with
I did notice that SortOrder got truncated if it wasn't cast to a longer
field, and quite frankly, I have no clue whether the sorting gets correct
if you don't cast it like this or if it was 'a random coincidence' that it
worked on the test data. I also tried to use cte rather than MyTable in
cte2 (
