My understanding is that that is supported behaviour. This page comes pretty close to saying that:
http://www.sqlite.org/autoinc.html But you still have the assumption that the rows will be inserted in the order the SELECT returns them. I find it very unlikely this would ever change, but I don't think it's written down anywhere. --- Michael Scharf <[EMAIL PROTECTED]> wrote: > Hi, > > I am using sqlite as embedded database. I am using > a UI to display the data. I have tables with up to > 1 million entries. The user can sort columns and filter > the data. To keep the memory footprint of the application > low I'm using a temporary table in the database for > each table view. I make the assumption that items added > in order to a table have their OID in that order > (this is true in sqlite 3.2.7). Then I just can fetch > the data of OID range that is visible in the UI. This > minimizes data transfer between the database and > the UI: > > -- my database > CREATE TABLE data ( > name TEXT; > number INTEGER; > ); > > CREATE INDEX dataNameIndex on data(name); > CREATE INDEX dataNumberIndex on data(number); > > -- The user sorts on NUMBER and selects NAME that > -- start with 'foo'. I create a temp table view1. > -- I make the assumption that view1.oid is in order > -- (1...size_of_the_table) > -- This is currently true with sqlite 3.2.7 > > CREATE TEMP TABLE view1 AS > SELECT oid AS dataid > FROM data > WHERE name like 'foo%' ORDER number; > > -- when some columns are displayed in the UI I fetch > -- the items. Say row 100-150 is to be displayed: > > SELECT * > FROM data > WHERE oid IN > (SELECT dataid > FROM view1 > WHERE rowid between 100 AND 150) > ORDER BY number; -- this sorting is needed, to > -- get the result in order! > > This work fine currently. The question is, is this an artifact > of the current implementation or can I rely on this in the > future? (I know that's not compatible with the SQL specs) > > Michael > > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
