I have a table in Oracle that contains some tasks and their dependent tasks. Some sample data.

I have a table in Oracle that contains some tasks and their dependent tasks. Some sample data.

The dependent task must always finish before the Task I want to generate an order of the above tasks like the following which the correct order of task execution:

Please can you provide a way to achieve this? I suppose i have to use CONNECT BY clause but i am not sure how to do this. Thanks in Advance.

• sql
• oracle-database
• plsql
• plsql-package

• Your example doesn't seem correct, since T3 depends on T2 and T4 depends on T3 – gimix Commented Nov 18, 2024 at 10:17
• @gimix you were right, the column names where wrong, i changed them, does it make sense to you know? – Panos_Koro Commented Nov 18, 2024 at 11:25

2 Answers 2

Additional info needed:
The explanation of the task here doesn't match the sample data and expected result ....

--      S a m p l e    D a t a :
Create Table tbl AS 
Select 'TASK3' as DEPENDENT, 'TASK2' as TASK From Dual Union All
Select 'TASK1', 'TASK5' From Dual Union All
Select 'TASK2', 'TASK5' From Dual Union All
Select 'TASK4', 'TASK3' From Dual;

The dependent task must always finish before the Task.

It seems that something is missing as additional logic to the above statement in order to clarify the issue ...

If quoted is true - then basic paths (orders) from dependant to task (with dependant suborder if any) are:

WITH 
  row_paths AS
    ( Select     t.DEPENDENT, t.TASK,  
                 t.DEPENDENT || '-' || t.TASK as TASK_ORDER,
                 o.DEPENDENT || '-' || o.TASK as TASK_SUBORDER
      From       tbl t
      Left Join  tbl o ON( o.TASK = t.DEPENDENT )
    )
  Select * From row_paths Order By TASK Desc, DEPENDENT

... if we replace dependant with coresponding path then the leaf to root paths would be like below ...

WITH 
  row_paths AS
    ( Select     t.DEPENDENT, t.TASK, 
                 t.DEPENDENT || '-' || t.TASK as TASK_ORDER,
                 o.DEPENDENT || '-' || o.TASK as TASK_SUBORDER
      From       tbl t
      Left Join  tbl o ON( o.TASK = t.DEPENDENT )
    ) 
Select     rp.*, 
           Case When rp.TASK_SUBORDER != '-' 
                Then REPLACE(rp.TASK_ORDER, rp.DEPENDENT, TASK_SUBORDER)
           Else rp.TASK_ORDER
           End as SELF_LEAF_TO_ROOT_PATH
From       row_paths rp
Order By   rp.TASK Desc, rp.DEPENDENT

... please provide some additional explanation of the logic that should be applied to fetch you the expected result ...

ADDITION:
One posible option to get your expected result could be the one that flags "root" elements, numbers the rows, counts rows per root, checks if the element is already finished and sorts accordingly... but that is just a blind guess - not the answer I could be sure it's the right one ...

WITH 
  grid AS
    ( Select   Distinct g.* 
      From ( Select     DEPENDENT, TASK, 1 as FLAG
             From       tbl
            UNION ALL
             Select t.TASK, t.TASK, 0
             From tbl t
             Where NOT EXISTS( Select 1 From tbl Where DEPENDENT = t.TASK )
           ) g
    )
SELECT  x.DEPENDENT as ALL_TASKS_ORDERED 
FROM   ( Select   g.*, 
                  Row_Number() Over( Partition By g.TASK Order By g.DEPENDENT ) as RN, 
                  Count(g.TASK) Over( Partition By g.TASK ) as CNT, 
                  Count(Case When g2.TASK Is Not Null Then 1 End) Over( Order By g.DEPENDENT ) as RN_DEPEND
         From     grid g
         Left Join grid g2 ON(g2.TASK = g.DEPENDENT And g.FLAG = 1)
      )  x
ORDER BY x.CNT Desc, x.RN, x.RN_DEPEND

R e s u l t :

fiddle

The sample data you provided is ambiguous and does not align with the outcome you desire or have specified.

Generally speaking, the clause:

CONNECT BY PRIOR Task = DependentTask
ORDER SIBLINGS BY Task;

is correct in principle, but it is uncertain whether it would be appropriate for your particular scenario given the issues outlined above.