If you want the fruits sorted (and not duplicated), why not just declare that
when defining the table?
create table fruits (id integer primary key, fruit text collate nocase unique);
and if you want to know the "relative row number" of the fruit simply have your
application count them:
logicalrow = 0
prepare('SELECT fruit from fruits order by fruit;')
while True:
if step() == NO MORE ROWS
break
fruitname = getcolumn(1)
logicalrecord++
playWithFruitAndLogicalRecordNumber()
wend
There is very little use (if any at all) for a "logical row number" is
Relational Data ...
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Shane Dev
>Sent: Monday, 20 November, 2017 14:31
>To: SQLite mailing list
>Subject: Re: [sqlite] how into insert row into middle of table with
>integer primary key
>
>Hi Ryan,
>
>Nice trick - changing the sign of ID. I agree that changing an
>existing
>record's ID value would cause problems for any other field / table
>that
>referenced this key.
>
>If I used your idea of adding a SortOrder column incremented in steps
>of x,
>as you suggested, the gaps would start to disappear after many
>inserts
>between existing records. I suppose the gaps could be reset by
>writing
>program to call sqlite3_exec with
>
>select * from fruit order by SortOrder;
>
>and increment a RowCount variable each time the callback was
>triggered,
>then update fruit with something like
>
>update fruit set SortOrder = RowCount*x where id = [id of the row
>when it's
>callback was triggered];
>
>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?
>
>
>On 20 November 2017 at 17:12, R Smith <rsm...@rsweb.co.za> wrote:
>
>> This question pops up from time to time.
>>
>> I will show a correct query script to achieve this below, but I
>want to
>> emphasize what others have said: Data in an RDBMS has no intrinsic
>order,
>> it's all SETs, and if you artificially bestow order to the data
>itself (as
>> opposed to the eventual output) then you are doing something that's
>very
>> bad in database design.
>>
>> To be specific, if the fruit in your DB needs ORDER as a property,
>best is
>> to add a column called SortOrder or FruitOrder or the like. In
>this column
>> you can then assign the values automatically in steps of 10 or 100,
>so you
>> end up with a table like:
>> id | fruit | SortOrder
>> 1 | Apple | 100
>> 2 | Pear | 200
>> 3 | Kiwi | 300 etc...
>>
>>
>> Then inserting:
>> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
>>
>> is simply trivial. (The 150 can be computed from splitting the
>difference
>> between the precedent and decedent). Non-Integer is best.
>>
>> Eventually though, you might need to do maintenance and reset the
>gaps or
>> such.
>>
>> Anyway, enough preaching - this query script will fix your
>Situation in
>> SQLite very fast:
>>
>> UPDATE fruit SET id = -id-1 WHERE id >= 2;
>> UPDATE fruit SET id = -id WHERE id < 0;
>> INSERT INTO fruit (2,'Banana');
>>
>>
>> Another way:
>>
>> UPDATE fruit SET id = -(id * 100);
>> UPDATE fruit SET id = -id WHERE id < 0;
>> INSERT INTO fruit (150,'Banana');
>>
>>
>> The reason why this is bad? Mostly a primary Key serves as a lookup
>for
>> other tables linking to a very specific record. Imagine your query
>that
>> added fruit to recipes where needed has the fruit's primary keys
>shuffled,
>> the next day will see some really weird recipes when Banana ends up
>where
>> Pear was intended. Next you'll want to insert Watermelon... :)
>>
>> Cheers,
>> Ryan
>>
>>
>> On 2017/11/19 10:37 PM, Shane Dev wrote:
>>
>>> Let's say I have a table of fruit -
>>>
>>> sqlite> .sch fruit
>>> CREATE TABLE fruit(id integer primary key, name text);
>>>
>>> with some entries -
>>>
>>> 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?
>>>
>>> desired result -
>>>
>>> sqlite> select * from fruit;
>>> 1|apple
>>> 2|banana
>>> 3|pear
>>> 4|kiwi
>>> _______________________________________________
>>> 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
>>
>_______________________________________________
>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
