Sort table entries in a special order

Question

I have a table:

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

id - primary key of automatic interruption.

nex_req - represents the order of entries. ( next_req = id )

How can I build a SQL query, get the 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 keep the order of records in a table. It's important for me.

Answer 1

If you can, change the table format. Instead of naming the following entry, mark the entries so that you can use the natural SQL type:

 +----+--------+------+ | 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 if you need for performance problems. And frankly, if you need to reorder strings, this is nothing more than a linked list of how you worked.

Answer 2

I can give you a solution in Oracle,

 select id,doc_id,next_req from table2 start with id = (select id from table2 where rowid=(select min(rowid) from table2)) connect by prior next_req=id 

fiddle_demo

Answer 3

I would suggest changing your table and adding another OrderNumber column, so in the end it will be easy to order in this column.

Although there may be problems with this approach:

1) You have an existing table and you need to set the values ​​of the OrderNumber column. I think this part is simple. You can simply set the initial zero values ​​and add a CURSOR, for example, moving through your records and increasing the number of your orders.

2) When a new row appears in your table, you need to change your OrderNumber, but here it depends on your specific situation. If you only need to add elements to the end of the list, you can set the new value as MAX + 1. In another situation, you can try writing TRIGGER when inserting new elements and invoke the same steps in step 1). This can greatly affect performance, so you should carefully study your architecture and possibly change this unusual design.