On 11 Feb 2013, at 3:45am, Igor Tandetnik <[email protected]> wrote:
> On 2/10/2013 10:06 PM, Mohit Sindhwani wrote: >> >> * 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. You're both wrong. Because someone may have done "PRAGMA reverse_unordered_selects = YES" <http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects> in which case you'll get records in a completely different order. As I'm sure you know, the SQL standard makes no assurances about any part of SELECT row order you don't specify. Not only that, but even if you do two identical SELECT statements in a row, you may get the rows in a different order. I only ever found one setup which did this (one where the database was distributed over different computers, and the order returned depended on which host was free at the time), but I wanted to flag it up as a possibility. As a response to the original poster, if you have your own intuitions about exactly how "RecNo" should behave, then make your own column called "RecNo" and put into it whatever you want to be there. If you just want to be sure that every row has a unique integer, you can refer to a magical invisible column called 'rowid' which will have one unless you messed with it yourself. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
