StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReordering of column data in mysql
text
Body
copied!<p>I have a table like so:</p> <pre><code>categoryID categoryName ---------------------------- 1 A 2 B 3 C </code></pre> <p>Now I want the user to be able to order this data according to his will. I want to remember his preferred order for future. So I thought I'd add a column <code>order</code> to the table above and make it of type <code>INT</code> and <code>AUTO_INCREMENT</code>. So now I get a table like this:</p> <pre><code>categoryID categoryName order ------------------------------------- 1 A 1 2 B 2 3 C 3 4 D 4 </code></pre> <p>My problem is - the user now decides, to bring <code>categoryName</code> with order 4 (D in example above) up to 2 (above B in example above) such that the table would now look like:</p> <pre><code>categoryID categoryName order ------------------------------------- 1 A 1 2 B 3 3 C 4 4 D 2 </code></pre> <p>My question is - How should I go about assigning new values to the <code>order</code> column when a reordering happens. Is there a way to do this without updating all rows in the table? </p> <p>One approach that comes to mind is to make the column a <code>FLOAT</code> and give it an order of 1.5 if I want to bring it between columns with order 1,2. In this case I keep loosing precision as I reorder items.</p> <p>EDIT: Another is to update all rows between (m, n) where m, n are the source and destination orders respectively. But this would mean running (m-n) separate queries wouldn't it?</p> <p>Edit 2: Assuming I take the <code>FLOAT</code> approach, I came up with this sql to compute the order value for an item that needs to be inserted after item with id = 2 (for example).</p> <pre><code>select (( select `order` as nextHighestOrder from `categories` where `order` > ( select `order` as targetOrder from `categories` where `categoryID`=2) limit 1) + ( select `order` as targetOrder from `categories` where `categoryID`=2)) / 2; </code></pre> <p>This gives me <code>3.5</code> which is what I wanted to achieve.</p> <p>Is there a better way to write this? Notice that <code>select order as targetOrder from categories where categoryID=9</code> is executed twice.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload