CODEWITHSUNDEEP
sqlpostgresqlrecursionrecursive-query
Alexander
Alexander

Reputation: 21

PostgreSQL order in recursive query

I try to get result of my query in natural order, but have a fail.

create table Tab2 (id int, F1 varchar(100));
insert into Tab2 values(1, '10,56,657,34,767,71');
insert into Tab2 values(3, '1,5487,27,9');
insert into Tab2 values(4, '11,13,37,2');
insert into Tab2 values(2, '12,6,65,8,67,22,70,5');


WITH RECURSIVE etc (id,  DataItem, F1)  AS (
                (SELECT id,
                LEFT(F1, strpos(concat(F1, ','), ',')-1) AS Part,
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),',')) AS Remainder
                FROM Tab2
                --ORDER BY Remainder
                )
    
 UNION ALL

                (SELECT id,
                LEFT(F1, strpos(concat(F1, ','), ',')-1),
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),','))
                FROM etc e
                WHERE F1 > ''
                --ORDER BY Dataitem
                 )
    )

SELECT id, row_number() over(partition BY id ORDER BY id) num, DataItem from etc ORDER BY id;

http://sqlfiddle.com/#!15/b0ccc6/89/0

Where is my mistake?

Upvotes: 1

Views: 608

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

Assuming that you want to get DataItem in order, in which it is placed in comma separated string, you can use another field to get an "index" (in below example it's a rowno).

For example:

id, dataitem
1, 10
1, 56
1, 657
...
1, 71
2, 12
...
2, 5
etc.

See:

WITH RECURSIVE etc (id, rowno,  DataItem, F1)  AS (
                (SELECT id, 1 as rowno,
                LEFT(F1, strpos(concat(F1, ','), ',')-1) AS Part,
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),',')) AS Remainder
                FROM Tab2
                )
    
 UNION ALL

                (SELECT id, rowno +1 as rowno,
                LEFT(F1, strpos(concat(F1, ','), ',')-1),
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),','))
                FROM etc e
                WHERE F1 > ''
                 )
    )

SELECT id, DataItem
from etc
ORDER BY id, RowNo;

SqlFiddle (after changes)

Upvotes: 0

user330315
user330315

Reputation:

If I understand your query correctly you are trying to get all elements from your (badly designed) comma separated string. There is no need to use a recursive query for that.

You can convert the string to array which can then be "unnested" into rows. Using the option with ordinality will also return the index of each element in the array which can be used in an order by to preserve the original order of the items in the string.

select t2.id, i.num, i.dataitem
from tab2 t2
  cross join unnest(string_to_array(f1,',')) with ordinality as i(dataitem, num)
order by t2.id, i.num;

Online example

Upvotes: 1

Related Questions