My thinking on this is use a trigger or two. So if you have fields Date and 
Tiebreaker (as mentioned in the other replies)

Date text,
Tiebreaker int,

Have it so if you insert it with a given Tiebreaker value it increments things 
after that, or if you give it null it puts it at the end of the list. Something 
like these (I think).

create trigger trg_tiebreaker_insert_with_value
before insert on table1
when new.Tiebreaker is not null
and exists (select 1 from table1 where Date = new.Date and Tiebreaker = 
 update table1
 set Tiebreaker = Tiebreaker + 1
 where Date = new.Date and Tiebreaker >= new.Tiebreaker;

create trigger trg_tiebreaker_insert_null
after insert on table1
when new.Tiebreaker is null
 update table1
 set Tiebreaker = ifnull((select max(Tiebreaker) from table1 where Date = 
new.Date), 0) + 1
 where rowid = new.rowid;

create index idx_my_ordering_index on table1 (Date, Tiebreaker);

-----Original Message-----
From: sqlite-users [] On 
Behalf Of Thom Wharton
Sent: Friday, October 14, 2016 9:29 AM
Subject: [sqlite] Inserting a new record (anywhere) into a table of ordered 
records that have an integer auto-increment primary key


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 

Is there a way to do this automagically (like a specialized INSERT 
command?) in Sqlite?


Thom Wharton

sqlite-users mailing list
sqlite-users mailing list

Reply via email to