Hi Peter,
I have been caught out on this.
On 11/2/2013 8:40 AM, Peter Aronson wrote:
You can add it to the select list as OID, ROWID or _ROWID_ or, if the
table has a column defined INTEGER PRIMARY KEY (but not INTEGER
PRIMARY KEY DESC) it'll also be this value. See:
http://www.sqlite.org/lang_createtable.html#rowid
There are a couple of very fine points here:
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.
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. 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.
In these small numbers, it can still be understood because you could say
that num=3 comes before num=4 and that's what the user should expect to
get. But remember that SQLite will behave the same way with large numbers
insert with num=2383831 #rowid = 2383831
insert with num=2343832 #rowid = 2343832
insert with num=4273 #rowid = 4273
insert with num=300029393 #rowid = 300029393
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.
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).
Depending on what problem the OP wants to solve, the solution is likely
to be a bit more complex.
Best Regards,
Mohit.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
