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 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
(select t3.id, cast(rpad(t3.SortText,10) as varchar(200)) SortOrder
 from MyTable t3
 where t3.id_parent is null
 union all
 select T2.id, cte2.SortOrder || rpad(T2.SortText,10)
 from MyTable T2
 join cte2 on cte2.ID = t2.id_parent)

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

Regards,
Josef