On Sat, Oct 15, 2016 at 10:18 AM, Stephen Chrzanowski <[email protected]> wrote:
> 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. > > CTEs can handle returning linked lists as a normal recordset.... > 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 < > [email protected]> 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 > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
