Sqlite db re-arrange row

Question

I am using a sqlite db in my Android application and I want my db to rearrange rows when user changes the order. It should affect all other rows so that I can show the data in order it was saved last time.

The image below shows my db. If I move the 2nd row to 6th position, my 3rd row should be 2nd, 4th should be 3rd and so on until 6th. How to do it using a query?

Answer 1

You can detect in your code (not sqlite) from which position to which other the item it's been moved. With that information you can create a sql statement to increment or decrement the affected positions. I mean (pseudocode):

user moves item from position n to position m

// increment position
if (m > n) {
    // move other items
    executeSQL("UPDATE table_name SET list_position = list_position-1 WHERE list_position > n AND list_position <= m");
    // move
    executeSQL("UPDATE table_name SET list_position = m WHERE list_position = n");
}
// decrement position
else if (m < n) {
    // move other items
    executeSQL("UPDATE table_name SET list_position = list_position+1 WHERE list_position >= m AND list_position < n");
    // move
    executeSQL("UPDATE table_name SET list_position = m WHERE list_position = n");
}