On Sat, Oct 15, 2016 at 10:18 AM, Stephen Chrzanowski <pontia...@gmail.com>
> 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
> 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
> > 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
> > 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,
> > 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@example.com
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> sqlite-users mailing list
sqlite-users mailing list