Question about enumeration

[Anon]

Hi!

I have some data records which are enumerated by an integer field. This field is neiter a key, nor AUTO_INCREMENT nor unique or anything. it's just a normal field ;-)

i get the records from the table in order of that numbers by using "SELECT * FROM Table ORDER BY FieldName;"

now i would like to be able to move records around that order!
for instance, i would like to be able to move record number 30 between record number 4 and 5. then of course, every number past record 5 has to be increased by one.

of couse i also want to be able to move a record forwards!

Does anybody know/have a solution for this problem?

Thanks in advance!

Jan

[Anon]

Jan,

I am hoping you do not want to move the records PHYSICALLY in the table - this would require extremely expensive table inserts.

The solution to renumber in a virtual sense is to get the enumeration for record thirty and change it to something between the enum for records 4 and 5, after shifting everything above 5 up by one.

eg record 4 has enum 100, record 5 has enum 100. record 30 has enum 800.

// pseudocode only! english!
if (enum 5 == enum 4) then
//shift all enums up by two
update enum set to enum+2 where record>4;
if (enum 5 == enum 4 +1) then
//shift all enums up by one
update enum set to enum+1 where record>4.

Then, reset record 30 with
update enum set to (value of enum 4)+1 where record == 30;

An update query is capable of setting a value in a row to itself plus one (easy), so the above is feasible.

Hope this helps.