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 that your cte is bottom-up and you try to sort by
something that is only available top-down. I.e. you need an additional
recursive query. With your testdata, I got your desired output like this:

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),
cte2 as /*since the parents doesn't have to be green, we cannot limit
them here*/
(select t3.id, t3.SortText SortOrder
 from MyTable t3
 where t3.parent_id is null
 union all
 select T2.id, cte2.SortOrder||'.'||T2.SortText
 from MyTable T2
 join cte2 on cte2.ID = t2.id_parent)

select distinct cte.id, cte.id_parent, cte.green, cte.SortText
from cte
join cte2 on cte.id = cte2.id
order by cte2.SortOrder

I fear it will be slow on huge tables, and I cannot guarantee it will
work with different data (e.g. can SortText contain some values that
makes the '.' in SortOrder mess up the sorting or are your actual data
equally nice as your example data?).

HTH,
Set