CODEWITHSUNDEEP
sqlsybase
Deepak Kumar
Deepak Kumar

Reputation: 863

SQL query for below scenario

I have a processDependency table which has thousands of records. Sample records are given below:

Proc DepProc
P1   P2
P2   P3
P3   P4
P4   P5
P6   P7

So when I run the SQL query I should be able to derive below:

  1. P1-P2-P3-P4-P5

  2. P6-P7

    Can anyone help me out with a generic SQL.

Upvotes: 0

Views: 70

Answers (1)

Hogan
Hogan

Reputation: 70523

This is not quite right because it gets all paths from Proc

But you did not make clear which one should be used first.

In any case it should give you an idea.

SELECT recurse AS
(
   SELECT Proc AS Start, 
          Proc||COALESCE('-'||DepProc,'') AS PATH, 
          DepProc as Next, 1 as Level
   FROM table

   UNION ALL

   SELECT Start,
          PATH||'-'||t.DepProc,
          t.DepProc as Next,
          Level+1 as Level
   FROM recurse r
   JOIN table t ON r.Next = t.Proc
), recurseWithMax AS
(
   SELECT PATH, Start, MAX(Level) OVER (Partition by Start) as Max, Level
)
SELECT Start, PATH
FROM recurseWithMax
WHERE Level = Max

Upvotes: 1

Related Questions