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 (since I thought that MyTable could be huge, whereas only a fraction could be green), but discovered that a recursive cte based on a recursive cte didn't work and hence, I didn't make them dependent on each other.
I think your query now seems good, and I hope that my fears that it will be too slow will not be a problem in your case. Set 2017-11-08 17:32 GMT+01:00 [email protected] [firebird-support] <[email protected]>: > > > 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 > > >
