On 2/10/2013 10:06 PM, Mohit Sindhwani wrote:
1. If I'm not wrong, ROWID does not change when you delete records. So, if the OP wants the ability to always say "it's the 9th record in the table right now", it won't work. Given that an RDBMS table is a set and in theory, sets don't have that property, actually, I don't think SQLite by itself can give you that number. What you need is actually something like a monotonically increasing number (say id) and then your query will need to take into account a way to count the number of records where id < my_record_id - an alternative could be to use the time stamp of insertion and do the same.
The OP reads records in a loop. I imagine he or she may simply use a loop counter as a sequence number, if that's what is needed. However, I too read the original question as asking for a unique identifier, such as ROWID, rather than a sequence number (which, as I said, is easy to obtain). The OP is asking for "record number that sqlite creates when it creates your record" - that is, the ID generated on row insertion, also known as ROWID.
2. The problem with INTEGER PRIMARY KEY.. it is not representative of what we expect from a ROWID. We expect ROWID to be a sequential number that increases with every record insertion.
Who do you mean "we", Kemo Sabe?
However, if you have something like num INTEGER PRIMARY KEY, you will be able to do this: insert with num=1 #rowid = 1 insert with num=2 #rowid = 2 insert with num=4 #rowid = 4 insert with num=3 #rowid = 3 It could be argued that the OP wanted the 3rd record to have a row number of 3, rather than 4.
Why then would the OP insert the row with num=4, rather than 3? Why do the extra work for the sole purpose of hurting your goal? That doesn't make much sense.
So, it doesn't give the concept of sequence at all since an INTEGER PRIMARY KEY needs to be unique, but does not have to be monotonically increasing.
Well, if you go out of your way to intentionally break the monotonically increasing sequence, then you end up with a broken sequence. Gun, meet foot.
I have been caught out by this - I read what the documentation says but just did not carefully understand it. What the above means is this: * You do an insert in the sequence as above, you say that I should not sort by id ASC because you want it in insertion order * You decide then to do a sort by ROWID ASC - expecting that ROWID is maintaining the sequence since when you do a general SELECT * from mytable; you often get it in insertion sequence (if I'm not wrong).
You are wrong. Normally, with a simple SELECT like this, you get the results ordered by ROWID. SQLite table is stored as a b-tree, with ROWID as its key, so that's the natural order of traversal.
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
