How to write SQL query to sort sequential data-CodePudding

Here is an example table:

id	content	previous	next
1	Preheat an oven to 220 degrees C.	NULL	2
2	Peel potatoes.	1	4
3	Toss sliced potatoes with oil.	4	6
4	Cut potatoes into slices.	2	3
5	Season the slices.	6	NULL
6	Bake in the oven for 30 minutes.	3	5

How to sort them in the correct order? I tried this:

SELECT
    t1.content,
    t2.content as next_step
FROM t_name t1
INNER JOIN t2
      ON t1.next_id = t2.id

The output still has the content in the same order, just added next_step column. I tried using SELECT content FROM t_name ORDER BY previous_id, still incorrect. How do I sort this table in sequential order?

To clarify what the output should be (see below). It can have an extra id column with the correct id number, but it is not required. The answer hint says using a self-join, but I am unable to get it done besides attempting a recursive cte.

content
Preheat an oven to 220 degrees C.
Peel potatoes.
Cut potatoes into slices.
Toss sliced potatoes with oil.
Bake in the oven for 30 minutes.
Season the slices.

CodePudding user response：

Here is how to do it with Oracle.

select           "content"
from             t
start with       "previous" is null
connect by prior "next" = "id"

content
Preheat an oven to 220 degrees C.
Peel potatoes.
Cut potatoes into slices.
Toss sliced potatoes with oil.
Bake in the oven for 30 minutes.
Season the slices.

Fiddle