Nsryjdtyk

I have some unordered data in a PostgreSQL database that looks like this:

ID  PATH    START   END

7   A123    B       C

4   B456    D       E

9   A123    A       B

2   B456    A       B

6   B456    B       C

21  A123    C       D

3   B456    C       D

5   B456    E       F

START and END values can't be ordered alphabetically, these have just been used to illustrate the issue

I am trying to achieve this:

id  path    sequence    start   end

9   A123    1           A       B

7   A123    2           B       C

21  A123    3           C       D

2   B456    1           A       B

6   B456    2           B       C

3   B456    3           C       D

4   B456    4           D       E

5   B456    5           E       F

The logic I am thinking of to achieve this to determine the start value (represented as A) for each path (A123 / B456). Then determine the sequence AB, BC, CD etc). Which needs to be repeated for all paths.

I've written recursive query that iterates through a given path name (See WHERE path = 'B456')

WITH RECURSIVE ordered(id, path, sequence, "start", "end") AS (

WITH path AS (SELECT id, "path", "start", "end"

FROM unordered

WHERE path = 'B456'),

startofpath AS (SELECT p1.id

FROM unordered p1

LEFT JOIN unordered p2 ON p1.start = p2.end

WHERE p2.start IS NULL)



--find start of path (A)

SELECT path.id, path.path, 1, path.start, path.end

FROM path, startofpath

WHERE path.id = startofpath.id

UNION ALL

--add on next path (B -> C)

SELECT path.id, path.path, ordered.sequence + 1, path.start, path.end FROM 

path

INNER JOIN ordered

ON path.start = ordered."end")

SELECT * FROM ordered

Sample Data:

CREATE table unordered (

id   INT PRIMARY KEY,

path TEXT NOT NULL,

"start" TEXT NOT NULL,

"end" TEXT NOT NULL);



INSERT INTO unordered (id, path, "start", "end") VALUES (7,'A123','B','C');

INSERT INTO unordered (id, path, "start", "end") VALUES (4,'B456','D','E');

INSERT INTO unordered (id, path, "start", "end") VALUES (9,'A123','A','B');

INSERT INTO unordered (id, path, "start", "end") VALUES (2,'B456','A','B');

INSERT INTO unordered (id, path, "start", "end") VALUES (6,'B456','B','C');

INSERT INTO unordered (id, path, "start", "end") VALUES (21,'A123','C','D');

INSERT INTO unordered (id, path, "start", "end") VALUES (3,'B456','C','D');

INSERT INTO unordered (id, path, "start", "end") VALUES (5,'B456','E','F');

What I am having trouble solving is then how to iterate through all paths (A123, then B456 etc)

Is anyone able to assist with this next step?
(or reworking my query from scratch if I've got the complete wrong idea)

Many thanks!

Is this what you are looking for ?

WITH RECURSIVE

get_path(id, path, sequence, starting, ending) AS (

    SELECT u.id, u.path, 1, u.starting, u.ending 

    FROM unordered AS u

    WHERE u.starting NOT IN (SELECT u.ending FROM unordered AS u) -- a starting point has no entry in ending column



    UNION



    SELECT u.id, u.path, g.sequence + 1, g.ending, u.ending

    FROM get_path AS g, unordered AS u

    WHERE u.starting = g.ending

)

TABLE get_path ORDER BY path, sequence;

Please note that i changed "start" into starting and "end" into ending.