I know the order of rows is possible and efficient if your query plan is using the special ROWID:
SELECT ROWID, col1, col2 from Table ORDER BY ROWID. the ORDER BY is a no-op in this case (see http://www.sqlite.org/queryplanner.html) Say you iterate 10 rows, and later want to continue where you left off.. you can just do the following: SELECT ROWID, col1, col2 from Table WHERE ROWID > curosr_row_id ORDER BY ROWID and this would be efficient In the more general case with a WHERE clause, which I believe you were referring to, then yes rows could come in any order, else you'd need an explcity ORDER BY, which would make the query more expensive. On Fri, Oct 29, 2010 at 9:11 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 29 Oct 2010, at 4:52pm, john Papier wrote: > > > The thing is, I need to keep a cursor to where in the > > table I was last searching, so I can continue the search from where I > left > > off, which is why using multiple tables was preferable; i.e., i can track > > the row_id, and then resume the search there (WHERE row_id > > cursor_row_id). > > This does not work in SQL. There is no concept of 'the order of rows in > the table' in SQL and an identical SELECT command is perfectly free to > return rows in a different order the second time around. > > If you want to maintain your own row orders, create and store your own row > numbers which do whatever /you/ want. The code for doing this doesn't > change much whether you have one huge table or many small ones. Either you > create another table to store the maximum current row numbers in or you use > the max(id) function to figure out the biggest number you've used so far. > > I suspect that the most efficient way to do this would be to have one big > table rather than many small ones, but I have no proof for your particular > application. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
