Algorithm for managing position in SQL table

Question

I have a small doubt, how to manage the following situation. In a DB table i save the relevant data in different columns and at the end i save the exact order in a column called position. This table is binded to a grid. Now I'm implementing the functionality that will make change the position to the rows, but I need still to update the DB. I was asking what is the best practice or the most convenient way to achieve this?

And suggestion is welcome.

Example

Name position

abc 1

def 2

ghj 3

Now I click on the button and I get the following

Name position

def 1

abc 2

ghj 3

I hope that I have explained myself!

Based on the comments this is the real example:

TABLE1 with 3 columns ID1, ID2, POSITION
ID1 AND ID2 are FK's and the table has a PK based on ID1 and ID2
When I select the data to display I use the following query SELECT NAME, ETC FROM TABLE2 INNER JOIN TABLE1 ON ID1 = ID1 WHERE ID2 = 511 ORDER BY POSITION

Now I need to change the position on two elements so the row in TABLE1 for example 311,511,5 needs to became 311,511,4 and the row that was 433,511,4 needs to became 433,511,5. The opposite.

I hope that this helped to clear out my question.

Cheers!

Is your button just moving an item up or down in the grid (basically changing its place with its neighbor)? Should you update each time a change happens or only on the save button? — zmilojko, Nov 15 '11 at 19:43
Each time they change the place I should update the DB and re-bind the grid. That was the idea. If you have a better one, feel free to suggest. Thanks! — MaiOM, Nov 15 '11 at 19:44
I don't know what grid you're using, but if it's truly bound to a recordset, does it not reorder itself when you change the data in the recordset? — Larry Lustig, Nov 15 '11 at 19:50
I yet need to update the data before I rebind it,and in order to update the data I need to find a way to update properly the DB. So if user clicked on up button in row 5,the data in the database should change in the way that row with a position column value 5 changes to 4 and the one that has value 4 changes to 5. Is there any convenient way to perform this operation or should I just use "brute force" check for the current position value, select the element that has a value selected minus one, update it on value plus one and that change the position value of selected element by subtracting it? — MaiOM, Nov 15 '11 at 19:55

score 7 · Accepted Answer · edited May 23 '17 at 12:03

When you say: "change the position to the rows", do you mean moving rows up and down through the table (relative to position ordering)?

If so, to move the row up, you need to exchange the position with previous row. To move it down, you need to do the same with the next row. Depending on your particular SQL dialect, the syntax for exchanging values from different rows will probably look similar to this:

UPDATE YOUR_TABLE
SET position =
    CASE position
    WHEN 2 THEN 3
    WHEN 3 THEN 2
    END
WHERE position IN (2, 3)

This example moves the row 3 up and row 2 down. Replace 2 and 3 according to your needs...

BTW, this should work even if there is a UNIQUE constraint on position (confirmed under Oracle).

For more ideas, you may take a look at this question.

score 1 · Answer 2 · edited Nov 15 '11 at 20:28

I'd create a table to save the results, in my example/solution the table to save is called 'ladders' and the data comes from 'data', and it limits by the top 100 entries. It first clears the table, then sets the temporary position variable to 0 and starts increasing/inserting.

"TRUNCATE `ladders`";SET @pos:=0;
INSERT INTO `ladders` ( `identifier`,`pos`,`score`)"
    " SELECT `id` , @pos := @pos + 1 , `thescore`"
    " FROM `data` ORDER BY `thescore`"
    " DESC LIMIT 100";

Algorithm for managing position in SQL table

2 Answers2

Linked