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.

I can suggest you to read [this Article](http://web.archive.org/web/20110606032941/http://dev.mysql.com/tech-resources/articles/hierarchical-data.html) — Teneff, Dec 21 '12 at 11:28
`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` — Maxim Cherkasov, Dec 21 '12 at 11:35
Your rows are essentially nodes of a linked list. SQL doesn't support this kind of data structure well at all — Bohemian, Dec 21 '12 at 12:18
possible duplicate of [Fetching linked list in MySQL database](http://stackoverflow.com/questions/675117/fetching-linked-list-in-mysql-database) — raina77ow, Dec 21 '12 at 13:27

score 1 · Answer 1 · answered Dec 21 '12 at 18:12

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.

score 0 · Answer 2 · answered Dec 21 '12 at 13:23

0

Am able to 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

answered Dec 21 '12 at 13:23

Mariappan Subramanian

9,527
8
32
33

score 0 · Answer 3 · answered Dec 21 '12 at 13:28

I'd suggest to modify your table and add another column OrderNumber, so eventually it would be easy to order by this column.

Though there may be problems with this approach:

1) You have existing table and need to set OrderNumber column values. I guess this part is easy. You can simply set initial zero values and add a CURSOR for example moving through your records and incrementing your order number value.

2) When new row appears in your table, you have to modify your OrderNumber, but here it depends on your particular situation. If you only need to add items to the end of the list then you can set your new value as MAX + 1. In another situation you may try writing TRIGGER on inserting new items and calling similar steps to point 1). This may cause very bad hit on performance, so you have to carefully investigate your architecture and maybe modify this unusual construction.

Sort table records in special order

3 Answers3