On 20 Nov 2017, at 9:31pm, Shane Dev <devshan...@gmail.com> wrote:
> I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? As others have written, the thing you’re trying to do is difficult. This is because there is not advantage to doing it in SQL. Why store integers at all ? If you want to maintain your own order using an in insertion list maintain a REAL field instead and do > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') This gives you a value of 1.5, and puts the new entry in the right place. When you want your fruit list produced in the right order, do SELECT name FROM fruits ORDER BY orderNumber and you’ll get your list in the right order. It doesn’t matter that the integers aren’t stored anywhere. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
