What you want to do is called using a linked list. Each record knows what its previous record is.
SQLite doesn't exactly have that capability directly. No SQL engine that I know of has the capability. Each row is unaware of any other row in that table. That row is a unique entity to itself, oblivious to anything outside itself. To answer your question, no, there is no SQL-FU that'll do what you want to do. To get what you want done, you must rely on your application to handle this. Back in the day, when using "recordset"s and "collections", we had to rely on extra variables to associate what the previous and next records in our list were supposed to be. So our recordset would have an extra integer to point to a memory location to where the next record was in memory. When I'd so this, since I knew what record I wanted to insert my new record after, I'd set a temporary variable to point to the old record I'd want to insert after, retrieve what that old records "next record" pointer is, set the old records "next record" to my the new record, then set my new records "next record" to what the old record "next record" was. You might have to do the same with this. You know you want to insert something between ID 2 and 3. So if your table had a reference field to what its next record was, you'd - retrieve row 2 to get its "next record" ID, - do the insert of your new data and set its "NextID" to some never to be used number (Like -1?) - figure out what record ID you put in (In this example, it'd be 6), then update row 2 to set its next value to 6. Then, theoretically in my head it works, when you make your call to retrieve your sorted list, you sort by your "NextID" field, not ID. Something like "order by NextID=-1,NextID". On Fri, Oct 14, 2016 at 9:29 AM, Thom Wharton < twhar...@northpointdefense.com> wrote: > Hello, > > I have a table of records in a Sqlite DB. It contains 5 records. Each > record has a time-stamp which is not guaranteed to be unique. To preserve > order (which is important in my project), I've given the table an integer > primary key (called ID) that is auto-increment. Let's say I have the > following table... > > > ID Date Type Size Data > > 1 10OCT-08:13:47 Ether 28 sddsgsd... > > 2 10OCT-08:13:52 Ether 77 fdasfdsdsddssdg... > > 3 10OCT-08:13:52 Ether 44 zeasfkkfa... > > 4 10OCT-08:13:57 Ether 33 dartdg... > > 5 10OCT-08:14:03 Ether 51 afafsfafa... > > > I want to be able to programmatically insert a new record anywhere in that > table. Let's suppose I want to create a new record between the records > whose ID are 2 and 3. This new record would need to take the ID of 3, and > all subsequent records would need to have their primary keys updated. > > Is there a way to do this automagically (like a specialized INSERT > command?) in Sqlite? > > Thanks, > > Thom Wharton > > > > _______________________________________________ > 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
