Ok, so I've broken down your problem into small, manageable steps. First, users are provided with a box to enter a value (e.g. pumpkin) and the 1)ability to select a ranking for pumpkin, or 2)the ability to select which old value (apefruit) the new value-pumpkin-belongs above/below (scenarios 1 and 2 are largely the same thing, just that 2 has an extra step so I'll assume 2 is your case) OR they can simply enter a new value (pumpkin) with no rank entry.
Pseudocode and logic: Step 1. If the input has no rank entry, get the max value myLocalRanking = select max(colA)+1 from fruitable skip to step 4 Step 2. Find out what value the apefruit has: myLocalRanking = select colB from fruitable where colB = 'apefruit' Step 3. Increment all values in cola greater than or equal to the apefruit value: update fruitable set colA = colA+1 where colA > myLocalRanking-1 Step 4. Insert the new value pumpkin with the myLocalRanking value: insert into fruitable (colA, colB) values(myLocalRanking, 'pumpkin') You mention an application so I assume that your application can handle this logic. In general, you could also do it with a stored_procedure/trigger. This solution has some problems to it with multi-user environments and corruption of the data if the process fails part way through. You should be able to mitigate the flaws in my solution by using an autoincremented value for colA (I think) but I'm not that familiar with the MySQL implementaiton of autoincrements, so I'll leave that to you. Also, you mentioned deleting, which would follow similar steps to the above except that you're decrementing everything above a certain value instead of incrementing it. And the final display I'm sure your familiar with as something like select colB from fruitable sort by colA ascending; Hope this helps, Greg sql, query -----Original Message----- From: kayamboo [mailto:[EMAIL PROTECTED]] I have a table with only one column and values are inserted by getting user request from my application. I want to display the column values in the order they are inserted. So only option is to go for another column with an auto increment key. eg. col A col B 1 apple 2 orange 3 apefruit But the user should have the option to insert data in between, any two existing column values. He can also delete any record. And now I want to display the records in the new order. eg. col B apple orange jack apefruit --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
