storing listview displaying position in database

I have created an android list view that allows users to rearrange the position of each item on it. however, I can't figure out how can I have the 'changed' position stored in the database in the best and most efficient way such that I will be able to retrieve a cursor from the datab开发者_开发百科ase by using a sort by displaying id function?

Can someone help me out with this? Thanks.

UPDATE:

Currently, what I am thinking of is to store the item position in the database as the last item position once it is created and after which when it is changed, i will change the position of the selected item in the database and run a loop to change every other items that is below it, to have an increment of 1.

does this logic make sense?

You could create a column in the database named ordinal (type integer), to store the order in which you want the item to appear in the list. When the user re-arranges the list, change the ordinal accordingly.

EDIT:

After reading your update, I do not think that will work because what if the user changes the position of something below the one you update?

Why not just update the position of every item according to the position in their ListView?

So one of your database methods would be:

public boolean updateOrdinal(int itemId, int ordinal)

{

    ContentValues c = new ContentValues();


    c.put("ordinal", ordinal);


    return db.update("TableName", c, kID + "=" + itemId, null) > 0;

}

This would be called after the user "saves" the list. Of course a loop would have to call this for each item.