CODEWITHSUNDEEP
mysqlsqlhibernate
Maxim Cherkasov
Maxim Cherkasov

Reputation: 115

Sort table records in special order

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.

Upvotes: 7

Views: 157

Answers (3)

Mark
Mark

Reputation: 1098

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.

Upvotes: 1

Ihor Deyneka
Ihor Deyneka

Reputation: 1409

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.

Upvotes: 0

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

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

Upvotes: 0

Related Questions