SQL chain same table : Select lines refering to the next one

Question

Consider a table with a column that reffers to another line of the same table:

|  id  |  previous_id  |  next_id  |
------------------------------------
|  10  |               |    11     |
|  11  |       10      |    12     |
...
|  x   |       y       |           |

The ids aren't always chained in sequence.
How do I query the next line until the next_id is null?
Is it possible to use while inside where?

Answer 1

You can use a recursive CTE. The exact syntax varies by database, but the idea is:

with recursive cte as (
      select id, next_id, 1 as lev
      from t
      where id = 10
      union all
      select cte.id, t.next_id, lev + 1
      from cte
      where cte join
            t
            on cte.next_id = t.id
     )
select cte.*
from (select cte.*, row_number() over (order by lev desc) as seqnum
      from cte
     ) cte
where seqnum = 1;

Answer 2

Option 1 - Hierarchical Query

You could use a recursive hierarchical query. Using oracle, this is called a hierarchical query.

Example of Oracle Hierarchical Query:

SELECT id, previous_id, next_id FROM tbl
   CONNECT BY PRIOR id= previous_id;

OR

SELECT id, previous_id, next_id FROM tbl
   CONNECT BY PRIOR id= next_id;

Option 2 - Recursive CTE

Available in both MS SQL Server, and MySQL 8. Gordon already provided a good example of this for MS SQL..

For MySQL 8 and previous, see: how-to-create-a-mysql-hierarchical-recursive-query.