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

2017-11-08 Thread 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
>
> 
>

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

2017-11-08 Thread 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

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

2017-11-07 Thread setysvar setys...@gmail.com [firebird-support] 
Hi again, Josef! I like SQL puzzles, and decided to spend a bit of time 
this afternoon trying to solve yours. Not using Fb 3, my knowledge of 
windowing functions is too limited to offer any such answer, but I found 
something that seems to get the result you want in Fb 2.5.

Your main problem is that your cte is bottom-up and you try to sort by 
something that is only available top-down. I.e. you need an additional 
recursive query. With your testdata, I got your desired output like this:

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 /*since the parents doesn't have to be green, we cannot limit 
them here*/
(select t3.id, t3.SortText SortOrder
  from MyTable t3
  where t3.parent_id is null
  union all
  select T2.id, cte2.SortOrder||'.'||T2.SortText
  from MyTable T2
  join cte2 on cte2.ID = t2.id_parent)

select distinct cte.id, cte.id_parent, cte.green, cte.SortText
from cte
join cte2 on cte.id = cte2.id
order by cte2.SortOrder

I fear it will be slow on huge tables, and I cannot guarantee it will 
work with different data (e.g. can SortText contain some values that 
makes the '.' in SortOrder mess up the sorting or are your actual data 
equally nice as your example data?).

HTH,
Set

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

2017-11-07 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
Please show how exactly resultset should be because i now do not understand 
your needs. Query return path and return in order a before b what more?

Regards,Karol Bieniaszewski
 Oryginalna wiadomość Od: "josef.gschwendt...@quattro-soft.de 
[firebird-support]" <firebird-support@yahoogroups.com> Data: 07.11.2017  08:53  
(GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support] 
Sorting-Problem on recursive query (window functions) 

 



  



  
  
  Hi,

thank you for your ideas.

But I think your solution would only work if the anchor query would select 
treemembers of level 1.

Whereas my query starts with "where Green =
'Yes'" and these elements are in different levels.

I have read that this should be possible with "window functions", but I can't 
find an example which works for me.

Regards,
Josef

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

2017-11-06 Thread josef.gschwendt...@quattro-soft.de [firebird-support] 
Hi,

thank you for your ideas.

But I think your solution would only work if the anchor query would select 
treemembers of level 1.

Whereas my query starts with "where Green = 'Yes'" and these elements are in 
different levels.

I have read that this should be possible with "window functions", but I can't 
find an example which works for me.

Regards,
Josef

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

2017-11-06 Thread 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Hi,

what about this?
with recursive cte as
  (select M.id, M.id_parent, M.green, CAST(M.SortText AS VARCHAR(100)) AS 
SortText
 from MyTable M
where M.Green = 'Yes'
union all
   select T2.id, T2.id_parent, T2.green, T2.SortText || '->' || C.SortText
 from MyTable T2
  join cte C on T2.ID = C.id_parent)
select distinct id, id_parent, green, SortText
  from cte
  order by SortText

regards,
Karol Bieniaszewski

From: josef.gschwendt...@quattro-soft.de [firebird-support] 
Sent: Monday, November 6, 2017 12:32 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Sorting-Problem on recursive query (window 
functions)

  

Hello,





I have a table which data is representing a tree:




CREATE TABLE MyTABLE (
ID INTEGER NOT NULL,
ID_PARENT INTEGER,
GREEN VARCHAR(3),
SortText VARCHAR(5),
CONSTRAINT PK_MYTABLE PRIMARY KEY (ID));



This is the data in this table:

  ID
 PARENT_ID
 GREEN
 SortText
 
  1
 
 No
 A3
 
  2
 1
 Yes
 B1
 
  3
 2
 No
 C6
 
  4
 2
 Yes
 C5
 
  5
 2
 Yes
 C4
 
  6
 1
 No
 B2
 
  7
 6
 No
 C4
 
  8
 6
 Yes
 C3
 
  9
 
 No
 A2
 
  10
 9
 No
 B3
 
  11
 10
 No
 C2
 
  12
 
 No
 A1
 
  13
 12
 Yes
 B4
 
  14
 13
 No
 C1
 



INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL, 'No', 
'A3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes', 'B1');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No', 'C7');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes', 'C6');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes', 'C5');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No', 'B2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No', 'C4');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes', 'C3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL, 'No', 
'A2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No', 'B3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No', 
'C2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL, 'No', 
'A1');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12, 'Yes', 
'B4');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No', 
'C1');



Table-Data shown as Tree

  A3
 
 
 

 B1
 
 

 
 C7
 

 
 C6
 

 
 C5
 

 B2
 
 

 
 C4
 

 
 C3
 
  A2
 
 
 

 B3
 
 

 
 C2
 
  A1
 
 
 

 B4
 
 

 
 C1
 

Now I need a dataset which suspends the green tree-nodes with the complete 
tree-path for each green cell. This dataset should be ordered alphabetically 
(A1 before A3 and C5 before C6)

  A1
 
 
 

 B4
 
 
  A3
 
 
 

 B1
 
 

 
 C5
 

 
 C6
 

 B2
 
 

 
 C3
 



I (almost) get the result I want with this statement:

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)



select distinct id, id_parent, green, SortText

  from cte







The dataset is ok, but the ordering is not (because there is no “Depth First 
by” – Clause)



With FB3 I tried to use windows functions (something like below):

Rank() over(Partition by id_parent order by SortText)



But I could not solve my problem!  - For instance, I got the message: Recursive 
member of CTE cannot use aggregate or window function.




Could anybody give me a hint how to solve this.




Thank you,


Josef

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

2017-11-06 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
What about something like:

with recursive cte as

  (select id, id_parent, green, SortText, SortText SortColumn

 from MyTable

where Green = 'Yes'

union all

   select T2.id, T2.id_parent, T2.green, T2.SortText, cte.SortColumn ||
t2.SortText

 from MyTable T2

  join cte on T2.ID  = cte.id_parent)



select id, id_parent, green, SortText

  from cte

  group by 1, 2, 3, 4

order by min(SortColumn)

Don't know whether or not it works, I use recursive CTEs very rarely and
haven't tried much sorting of them.

HTH,

Set

2017-11-06 12:32 GMT+01:00 josef.gschwendt...@quattro-soft.de
[firebird-support] :

>
>
> Hello,
>
>
> I have a table which data is representing a tree:
>
>
> CREATE TABLE MyTABLE (
> ID INTEGER NOT NULL,
> ID_PARENT INTEGER,
> GREEN VARCHAR(3),
> SortText VARCHAR(5),
> CONSTRAINT PK_MYTABLE PRIMARY KEY (ID));
>
> This is the data in this table:
>
> ID
>
> PARENT_ID
>
> GREEN
>
> SortText
>
> 1
>
>
>
> No
>
> A3
>
> 2
>
> 1
>
> Yes
>
> B1
>
> 3
>
> 2
>
> No
>
> C6
>
> 4
>
> 2
>
> Yes
>
> C5
>
> 5
>
> 2
>
> Yes
>
> C4
>
> 6
>
> 1
>
> No
>
> B2
>
> 7
>
> 6
>
> No
>
> C4
>
> 8
>
> 6
>
> Yes
>
> C3
>
> 9
>
>
>
> No
>
> A2
>
> 10
>
> 9
>
> No
>
> B3
>
> 11
>
> 10
>
> No
>
> C2
>
> 12
>
>
>
> No
>
> A1
>
> 13
>
> 12
>
> Yes
>
> B4
>
> 14
>
> 13
>
> No
>
> C1
>
>
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL,
> 'No', 'A3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes',
> 'B1');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No',
> 'C7');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes',
> 'C6');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes',
> 'C5');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No',
> 'B2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No',
> 'C4');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes',
> 'C3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL,
> 'No', 'A2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No',
> 'B3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No',
> 'C2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL,
> 'No', 'A1');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12,
> 'Yes', 'B4');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No',
> 'C1');
>
>
>
> Table-Data shown as Tree
>
> A3
>
>
>
>
>
>
>
> B1
>
>
>
>
>
>
>
> C7
>
>
>
>
>
> C6
>
>
>
>
>
> C5
>
>
>
> B2
>
>
>
>
>
>
>
> C4
>
>
>
>
>
> C3
>
> A2
>
>
>
>
>
>
>
> B3
>
>
>
>
>
>
>
> C2
>
> A1
>
>
>
>
>
>
>
> B4
>
>
>
>
>
>
>
> C1
>
> Now I need a dataset which suspends the green tree-nodes with the
> complete tree-path for each green cell. This dataset should be ordered
> alphabetically (A1 before A3 and C5 before C6)
>
> A1
>
>
>
>
>
>
>
> B4
>
>
>
> A3
>
>
>
>
>
>
>
> B1
>
>
>
>
>
>
>
> C5
>
>
>
>
>
> C6
>
>
>
> B2
>
>
>
>
>
>
>
> C3
>
>
>
>
> *I (almost) get the result I want with this statement: *
> 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)
>
>
>
> select distinct id, id_parent, green, SortText
>
>   from cte
>
>
>
>
>
>
>
> The dataset is ok, but the ordering is not (because there is no “Depth
> First by” – Clause)
>
>
>
> With FB3 I tried to use windows functions (something like below):
>
> Rank() over(Partition by id_parent order by SortText)
>
>
>
> But I could not solve my problem!  - For instance, I got the message:
> Recursive member of CTE cannot use aggregate or window function.
>
>
> Could anybody give me a hint how to solve this.
>
>
> Thank you,
>
> Josef
>
>
>
>
> 
>