[thelist] Interesting SQL Puzzle

Joshua Olson joshua at waetech.com
Sat Aug 20 11:31:38 CDT 2005


> -----Original Message-----
> From: Frank
> Sent: Saturday, August 20, 2005 11:12 AM
> 
> 
> 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.
> 
> 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.

Frank,

Assume your update is something like this:

UPDATE table
SET rank = rank + 1
WHERE rank >= 33

The update will be really fast, even with a large number of records.  Since
this is a web application, I'm going to jump out on a limb and assume that
SELECTs far out number the UPDATEs and INSERTs.  If this is true, then even
if this WAS somewhat slow it wouldn't be a big deal in the big picture.

> Seems to me that updating 64 records with a different 
> rank might be rather inefficient, especially if we have a 
> lot of records.

BTW, 64 records is always 64 records, even IF you have lots of records.  ;-)

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/




More information about the thelist mailing list