Self-referential table ordering in SQL

Question

Given a table like this:

taskId	nextTaskId
1	2
2	3
3	6
4	5
5	NULL
6	7
7	8
8	4

I need the following order for output:

taskId	nextTaskId
1	2
2	3
3	6
6	7
7	8
8	4
4	5
5	NULL

Is there any way to do this via MySQL query?

Not sure if you can do it in SQL. If you can, it will probably require a recursive CTE. — Barmar, Jun 15 '22 at 01:13
Refer - https://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last — Pankaj, Jun 15 '22 at 01:17

score 0 · Answer 1 · answered Jun 15 '22 at 04:27

Using recursive CTE -

WITH RECURSIVE
self_ref_cte ( taskid, nexttaskid )
AS
( SELECT taskid, nexttaskid
          FROM self_ref WHERE taskid = 1
  UNION ALL
  SELECT s.taskid,  s.nexttaskid 
         FROM self_ref_cte c JOIN self_ref s
        ON s.taskid = c.nexttaskid 
)
SELECT * FROM self_ref_cte;

DB fiddle here.

Self-referential table ordering in SQL

1 Answers1