[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