Leading on from what Keith suggests above re: 'and join' can we simplify as:
Findfirst: SELECT * FROM some_table WHERE original_expression AND new_expression LIMIT 1; What the LIMIT 1 will return depends on what index/primary key is in effect, sort of hoping for rowid here :-) This appears equivalent to the temp_table from query + new_query approach, but perhaps there is something you have omitted that makes this invalid??? Also not not sure where you are getting your 'X' value for 'next' and 'previous' processing. as there is no real find operation (which would likely return multiple records). If this is being stored, perhaps in external program code, then fine, there is a clear concept of current record and therefore next and previous become trivial (less than, greater than current, being X). FindPrevious: SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression AND rowid < X ) ); FindNext: SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression AND rowid > X ) ); FindLast: SELECT * FROM some_table WHERE id = ( SELECT max(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression ) ); You can of course use the temp table approach, but sqlite I believe creates a temp table to facilitate the nested SELECT so we are indirectly using the temp approach......hope this helps. regards, Michael.j.Falconer. On Fri, May 24, 2013 at 10:38 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > > And, can I depend on SQLite to generate results in the same order as the > > original prepare/step sequence and the temp table generation, ie. is the > > temp table's rowid going to be consistent with the original step order? > > If you use an ORDER BY clause, yes. If not, then the rows are returned in > random order. > > As for the rest of your questions, why do you not just join the search > conditions with an AND? > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
