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