Re: [firebird-support] Re: wrong ordering in WITH RECURSIVE query

2019-04-16 Thread Richard Damon rich...@damon-family.org [firebird-support] 
On Apr 16, 2019, at 4:40 AM, respekt...@post.cz [firebird-support] 
 wrote:
> 
> Hi, thank you for reply
> 
> But this ordering totaly damage the tree structure
> 
> Because PORADI is only relative to parent node, so all child nodes starting 
> with 1
> 
> If I change the query as you recommend, the set is sorted in way
> 1. all first nodes
> 2. all second nodes
> 3. all third nodes
> 
> 
> Any other advices??
> 
> Thank you,
> Tomas
It sounds like you are forgetting that in SQL, the order of rows is considered 
unimportant unless there is an explicit order specified AT THAT QUERY (and not 
in a sub-query used to build that query). That is the nature of the language. 
If you want the rows in a particular order, then you need to generate something 
that give it that order.

Any order outside that rule is just a coincidence.

For instance, a sort code for the 3rd node below the 2nd node below node 1 
could be 1.0203 (assuming that no node has more than 99 children). Or, if no 
node has more than 26 children, something like ABC

[firebird-support] Re: wrong ordering in WITH RECURSIVE query

2019-04-16 Thread respekt...@post.cz [firebird-support] 
Hi,
 I've found the reason :-)
 

 create view view_strom_pujcoven
with recursive n (ID_PUJC, NAZEV, FK_BUDOVA, PK_PUJCOVNA, PORADI, 
FK_UCETNI_JEDNOTKA, JE_UK, SIGLA, JE_ONLINE, ID_BUDOVA_PUV, FK_NADR, JE_EXEMP, 
uroven, razeni)
as (
  SELECT ID_PUJC, NAZEV, FK_BUDOVA, PK_PUJCOVNA, PORADI, FK_UCETNI_JEDNOTKA, 
JE_UK,SIGLA, JE_ONLINE, ID_BUDOVA_PUV, FK_NADR, JE_EXEMP, 0, cast('0' as 
varchar(1024)) FROM def_pujc WHERE fk_nadr is null
  UNION ALL
  SELECT dp.ID_PUJC, dp.NAZEV, dp.FK_BUDOVA, dp.PK_PUJCOVNA, dp.PORADI, 
dp.FK_UCETNI_JEDNOTKA, dp.JE_UK, dp.SIGLA, dp.JE_ONLINE, dp.ID_BUDOVA_PUV, 
dp.FK_NADR, dp.JE_EXEMP, n.uroven + 1, cast(n.razeni || '.' || lpad(dp.poradi, 
4, '0') as varchar(1024)) FROM def_pujc dp, n WHERE dp.fk_nadr = n.id_pujc
) SELECT * FROM n order by razeni
 

 it generates column RAZENI with content
 0
 0.0001
 0.0001.0001
 0.0001.0002
 0.0002
 0.0002.0010
 0.0002.0015
 ...
 

 It works till the PORADI is less than 10 000 and there is less than 200 levels
 

 Best regards, Tomas

Re: [firebird-support] Re: wrong ordering in WITH RECURSIVE query

2019-04-16 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
It's your query that is wrong, it's the final result that should be
ordered, not the subselect, i.e.:

with recursive n (ID_PUJC, NAZEV, PORADI, FK_NADR, uroven)
as (
  SELECT ID_PUJC,NAZEV,PORADI,FK_NADR,0 FROM def_pujc_test
WHERE fk_nadr is null
  UNION ALL
  SELECT dp.ID_PUJC, dp.NAZEV, dp.PORADI, dp.FK_NADR, n.uroven + 1 FROM
def_pujc_test dp, n WHERE dp.fk_nadr = n.id_pujc
)
SELECT * FROM n
order by poradi

Set

tir. 16. apr. 2019 kl. 02:42 skrev respekt...@post.cz [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Hi all,
>
> it is ordered according to order of inserts
>
> I have tried to delete one row and reinsert it - and the row is now last
> :-(
>
> So is there some logical error in my query?
> Or is it the error in fb engine?
>
> Thank you a lot.
> Tom
>
>
>

[firebird-support] Re: wrong ordering in WITH RECURSIVE query

2019-04-15 Thread respekt...@post.cz [firebird-support] 
Hi all,
 

 it is ordered according to order of inserts
 

 I have tried to delete one row and reinsert it - and the row is now last :-(
 

 So is there some logical error in my query?
 Or is it the error in fb engine?
 

 Thank you a lot.
 Tom