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
