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 = new.Tiebreaker) begin update table1 set Tiebreaker = Tiebreaker + 1 where Date = new.Date and Tiebreaker >= new.Tiebreaker; end; create trigger trg_tiebreaker_insert_null after insert on table1 when new.Tiebreaker is null begin update table1 set Tiebreaker = ifnull((select max(Tiebreaker) from table1 where Date = new.Date), 0) + 1 where rowid = new.rowid; end; create index idx_my_ordering_index on table1 (Date, Tiebreaker); -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thom Wharton Sent: Friday, October 14, 2016 9:29 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key 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
