Sort table records in special order

Question

I have table:

+----+--------+----------+
| id | doc_id | next_req | 
+----+--------+----------+
|  1 |    1   |     4    | 
|  2 |    1   |     3    | 
|  3 |    1   |     0    | 
|  4 |    1   |     2    | 
+----+--------+----------+

id - auto incerement primary key.

nex_req - represent an order of records. (next_req = id of record)

How can I build a SQL query get records in this order:

+----+--------+----------+
| id | doc_id | next_req | 
+----+--------+----------+
|  1 |    1   |     4    | 
|  4 |    1   |     2    | 
|  2 |    1   |     3    | 
|  3 |    1   |     0    | 
+----+--------+----------+

Explains:

record1 with id=1 and next_req=4 means: next must be record4 with id=4 and next_req=2
record4 with id=5 and next_req=2 means: next must be record2 with id=2 and next_req=3 
record2 with id=2 and next_req=3 means: next must be record3 with id=1 and next_req=0 
record3 with id=3 and next_req=0: means that this is a last record 

I need to store an order of records in table. It's important fo me.

Answer 1

If you can, change your table format. Rather than naming the next record, mark the records in order so you can use a natural SQL sort:

+----+--------+------+
| id | doc_id | sort | 
+----+--------+------+
|  1 |    1   |  1   | 
|  4 |    1   |  2   | 
|  2 |    1   |  3   | 
|  3 |    1   |  4   | 
+----+--------+------+

Then you can even cluster-index on doc_id,sort for if you need to for performance issues. And honestly, if you need to re-order rows, it is not any more work than a linked-list like you were working with.