SQL in any flavor isn't going to get what you want directly, as you've seen in these posts.
The data is in your database for a reason. Its data. It isn't supposed to be manipulated in the way you want to do in this thread. Displaying something in a particular order is done at the application level, not the database level. There is an an 'order by' clause, but your use is somewhat invalid. I've used "order by" statements that push things either to the top or bottom of a list when I'm sorting alphabetically (IE: select * from ComponentList order by Enabled=1,Caption;) but that is the most advanced I've ever gotten as I know that the UI should be responsible for dealing with an ordering issue. "ID" fields should never be given to the user. Its a way to identify that a piece of information in your database relates directly to a UI element, something in a list in memory, or whatever. Its not a means to order by anything, but a way to identify. Its not a human consumable bit of information, and shouldn't be managed by a user. In situations where I need to put items in a certain order that isn't alphabetical or numerical sorts (Beyond what I mentioned above), I dig into linked lists and have my application code deal with displaying the information in the order of that list. The application also specifies the relationship between the previous, current, and next item. My application makes a simple select from the database, with no order specified, asking for the items ID, its parent, and its child, and put it into list of custom record sets or classed objects. I loop through the the list I pulled out looking for the item that has ParentID=0 and stake claim that this is the top of the list. Now, when I want to display information from the list, I know where my first/top item is, and I know where I need to go from there to get the rest of the list. When ChildID=0, I know I'm at the end of the list. While working only in memory, if I need to insert information between two items, I get the ParentID and ChildID from the item I want to insert after. I get a new ID either by inserting the record into the database to get the last_insert_id or I look at the largest ID in my recordset, then +1 it. I change the ChildID of the previous record to the new item ID, I change the ParentID of the ChildID record to the new item ID, and then I set the new item IDs parent and child IDs to the ID of the parent and child respectively. If I were using a class, a function would do this for me in memory, and, write the changes to the database, then update the UI however needed. On Sun, Nov 19, 2017 at 3:37 PM, Shane Dev <devshan...@gmail.com> wrote: > Let's say I have a table of fruit - > > sqlite> .sch fruit > CREATE TABLE fruit(id integer primary key, name text); > > with some entries - > > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? > > desired result - > > sqlite> select * from fruit; > 1|apple > 2|banana > 3|pear > 4|kiwi > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
