Sorry if this is confusing, it is somewhat difficult to explain. I find myself frequently creating solutions to the same problem. I'm not yet happy with the way I've done any of them and I'd like to find a purely SQL way of doing this if possible.
Here's what I have. For a contrived illustration, let's say we have a database of photo galleries, each having some number of images. Our tables would look like this: galleries ------------------------- galleryid | int4 (pkey) name | text images ------------------------- imageid | int4 (pkey) galleryid | int4 (fkey) image | text dsply_order | int4 (index) Now, the same database holds many different galleries. Each gallery has some number of images and the users want the images to show in a certain order. This is done by inserting the images in the order you want them to appear. However, it may be necessary for the user to re-order them. I provide a MOVE UP, MOVE DOWN, MOVE TO TOP and MOVE TO BOTTOM option so that they can change the order. Also, people occasionally delete images. If a person maintaining galleryid 1 which has 6 images, a "SELECT * FROM images WHERE galleryid = 1 ORDER BY dsply_order" might show this: imageid | galleryid | image | dsply_order --------+-----------+----------+------------ 4 | 1 | 1/me.gif | 1 7 | 1 | 1/aa.gif | 2 12 | 1 | 1/bb.gif | 3 11 | 1 | 1/cc.gif | 4 15 | 1 | 1/dd.gif | 5 18 | 1 | 1/ee.gif | 6 Now, when a person decide to re-order, it's no problem to do this: To move imageid 12 to the top: UPDATE images SET dsply_order = CASE WHEN imageid = 12 THEN 1 ELSE dsply_order + 1 END WHERE galleryid = 1; That however leaves a gap at dsply_order 4: imageid | galleryid | image | dsply_order --------+-----------+----------+------------ 12 | 1 | 1/bb.gif | 1 4 | 1 | 1/me.gif | 2 7 | 1 | 1/aa.gif | 3 11 | 1 | 1/cc.gif | 5 15 | 1 | 1/dd.gif | 6 18 | 1 | 1/ee.gif | 7 Similar problem occurs when someone deletes an item. Ideally, I'd like to figure out a single SQL query that can be run afterwards to clean up the dsply_order to make sure that each number occurs only one time and that there are no gaps. I know I can write a sp for this, but the problem is, I do this very task on lots of different tables that all have different formats and different types of data. I'd rather not have to maintain many different procedures if I can find an alternate. Right now, I get the job done in code, but it feels inefficient. Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by www.followers.net | recomending Elite CMS to your customers! [EMAIL PROTECTED] | http://www.followers.net/isp ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
