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

Dependent Task	Task
TASK3	TASK2
TASK1	TASK5
TASK2	TASK5
TASK4	TASK3

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

Dependent Task	Task
TASK3	TASK2
TASK1	TASK5
TASK2	TASK5
TASK4	TASK3

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:

All_Tasks_Ordered
TASK1
TASK2
TASK5
TASK4
TASK3

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

Share Improve this question Follow edited Nov 18, 2024 at 17:09 General Grievance 5,0433838 gold badges3737 silver badges5656 bronze badges asked Nov 18, 2024 at 10:09 Panos_KoroPanos_Koro 5977 bronze badges 2

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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;

DEPENDENT	TASK
TASK3	TASK2
TASK1	TASK5
TASK2	TASK5
TASK4	TASK3

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

DEPENDENT	TASK	TASK_ORDER	TASK_SUBORDER
TASK1	TASK5	TASK1-TASK5	-
TASK2	TASK5	TASK2-TASK5	TASK3-TASK2
TASK4	TASK3	TASK4-TASK3	-
TASK3	TASK2	TASK3-TASK2	TASK4-TASK3

... 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

DEPENDENT	TASK	TASK_ORDER	TASK_SUBORDER	SELF_LEAF_TO_ROOT_PATH
TASK1	TASK5	TASK1-TASK5	-	TASK1-TASK5
TASK2	TASK5	TASK2-TASK5	TASK3-TASK2	TASK3-TASK2-TASK5
TASK4	TASK3	TASK4-TASK3	-	TASK4-TASK3
TASK3	TASK2	TASK3-TASK2	TASK4-TASK3	TASK4-TASK3-TASK2

... 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 :

ALL_TASKS_ORDERED
TASK1
TASK2
TASK5
TASK4
TASK3

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.