| Subject | Re: [firebird-support] Sorting-Problem on recursive query (window functions) |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2017-11-09T07:54:18Z |
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 Josef.Gschwendtner@... [firebird-support] <firebird-support@yahoogroups.com>:
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