[thelist] Interesting SQL Puzzle

Frank lists at frankmarion.com
Sat Aug 20 10:11:51 CDT 2005

I've got a situation where I have 100 records. Each record has a field 
named "rank" whose numeric value represents the record's position in a 
list.  If the user wants to move a record to a different location on the 
list, s/he can change it.

My puzzle is this: a user enters a 101st record and wants it to be located 
as item 33 in the list, thus, record with the current value of 33 would 
be  updated to #34, and so on down the line. If a record is deleted, all 
the higher valued records would have to be re-updated.

(Tangent: if sorting by rank. the last statement is not necessarily true, 
36 is still higher than 34, but I can see potential issues arising if it's 
not kept tight)

How do I go about re-ordering the records to prevent collision? Seems to me 
that updating 64 records with a different rank might be rather inefficient, 
especially if we have a lot of records.

Can anyone suggest a different method?

Frank Marion     lists at frankmarion.com      Keep the signal high.

More information about the thelist mailing list