Re: [firebird-support] Sorting-Problem on recursive query (window functions)

[josef.gschwendt...@quattro-soft.de [firebird-support]]

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

[firebird-support] firebird 2.5.2

[Ahmad Alammar / vamos Eltern-Kind-Reisen GmbH alam...@vamos-reisen.de [firebird-support]]

Hello all,

I have firebird 2.5.2 installed on windows server 2008 R2 64, I want to upgrade 
the windows Server to 2016,
does firebird 2.5.2  work on windows server 2016?!
if No, which version of firebird I need.


Kind regarrds
Viele Grüße aus Hannover

Ahmad Alammar
Medien & EDV

vamos Eltern-Kind-Reisen GmbH
Hindenburgstr. 27, 30175 Hannover

Tel. +49 511 400 799-71
Fax +49 511 400 799-99
alam...@vamos-reisen.de

www.vamos-reisen.de
www.vamos-geheimtipps.de
www.vamos-blog.de

Bürozeiten: Mo. bis Fr. 09.00 bis 18.00 Uhr, Sa. 09.00 bis 14.00 Uhr

Geschäftsführer: Stephan Krug und Jens Kubicki | Handelsregister Hannover Abt. 
B Nr. 52723 | USt-IdNr. DE115647989
Sparkasse Hannover | IBAN: DE88 2505 0180  1251 99 | BIC: SPKHDE2H

Re: [firebird-support] Sorting-Problem on recursive query (window functions)

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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.gschwendt...@quattro-soft.de
[firebird-support] :

>
>
> 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
>
> 
>