PostgreSQL WITH RECURSIVE query to get ordered parent-child chain by a Partition Key

Question

I have the issue writing a sql script on PostgreSQL 9.6.6 which orders steps in a process by using the steps' parent-child ID's, and this grouped/partitioned per process ID. I couldn't find this special case here, so I apologize if I missed it and would please you to provide me the link to the solution in the comments.

The case: I have a table which looks like this:

processID | stepID | parentID
     1         1          NULL
     1         3           5
     1         2           4
     1         4           3
     1         5           1
     2         1          NULL
     2         3           5
     2         2           4
     2         4           3
     2         5           1

Now I have to order the steps by starting with the step where parentID is NULL for each processID .

Note: I cannot simply order StepID or parentID as new steps I put within the whole process get a higher stepID then the last step in the process (continuous generating surrogate key).

I have to order the steps for every processID, that I will receive the following output:

processID | stepID | parentID
     1         1          NULL
     1         5           1
     1         3           5
     1         4           3
     1         2           4
     2         1          NULL
     2         5           1
     2         3           5
     2         4           3
     2         2           4

I tried to do this with the CTE function WITH RECURSIVE:

    WITH RECURSIVE
starting (processID,stepID, parentID) AS
  (
    SELECT b.processID,b.stepID, b.parentID
    FROM process b
    WHERE b.parentID ISNULL
  ),
  descendants (processID,stepID, parentID) AS
  (
    SELECT b.processID,b.stepID, b.stepparentID
    FROM starting b
    UNION ALL
    SELECT b.processID,b.stepID, b.parentID
    FROM process b

    JOIN descendants AS c ON b.parentID = c.stepID
)
SELECT * FROM descendants

The result is not what I am searching for. As we have hundreds of processes, I receive a list where the first records are the different processIDs which have a NULL value as parentID.

I guess I have to recursive the whole script on the processID again, but have no idea how.

Thank you for your help!

score 3 · Accepted Answer · answered Sep 26 '18 at 13:25

You should calculate the level of each step:

with recursive starting as (
    select processid, stepid, parentid, 0 as level
    from process
    where parentid is null
union all
    select p.processid, p.stepid, p.parentid, level+ 1
    from starting s
    join process p on s.stepid = p.parentid and s.processid = p.processid
)
select *
from starting
order by processid, level

 processid | stepid | parentid | level 
-----------+--------+----------+-------
         1 |      1 |          |     0
         1 |      5 |        1 |     1
         1 |      3 |        5 |     2
         1 |      4 |        3 |     3
         1 |      2 |        4 |     4
         2 |      1 |          |     0
         2 |      5 |        1 |     1
         2 |      3 |        5 |     2
         2 |      4 |        3 |     3
         2 |      2 |        4 |     4
(10 rows)

Of course, you can skip the last column in the final select if you do not need it.

PostgreSQL WITH RECURSIVE query to get ordered parent-child chain by a Partition Key

1 Answers1