I have problem I'm trying to solve. I have two tables: one is a list of tasks, like this:

I have problem I'm trying to solve. I have two tables: one is a list of tasks, like this:

The second table is a mapping table. It maps one task to the task that needs to be completed before the first task can be worked on. It's setting up a dependency. There are only 3 rows because we're just talking about dependencies between tasks.

The order of the tasks should be:

1
3
2
4

How would I retrieve that using T-SQL in a SQL Server environment?

• sql
• sql-server
• sorting

2 Answers 2

You don't need table2 at all. In Table1, add a column called ExecutionOrder (int not null).

For task id 1, executionorder = 1

For task id 2, executionorder = 3

For task id 3, executionorder = 2

For task id 4, executionorder = 4

When you read from the table, sort by executionorder. That's all. Don't overcomplicate this with an additional table.

You can solve this with a recursive CTE

fiddle

--Navigate tree, sort by level
WITH cte (TaskID,  Task, DEPENDENTTASKID, Level) AS 
(
    SELECT T1.TaskID, T1.Task,
           CAST('' as VARCHAR(512)) as DEPENDENTTASKID, 
           0 AS Level
    FROM  TABLE1 T1
    LEFT JOIN Table2 T2 ON T1.taskID=T2.TaskID
    WHERE T2.DEPENDENTTASKID IS NULL

    UNION ALL 

    SELECT T2.TaskID,  
           t1.Task,
           T2.DEPENDENTTASKID, 
           cte.Level + 1 AS Level
    FROM  Table1 T1
    INNER JOIN Table2 T2 On T1.TaskID=T2.TaskID
    INNER JOIN cte ON cte.TaskID = T2.DEPENDENTTASKID 
) 
SELECT TaskID, Task
FROM cte
ORDER BY Level ASC