OOoops ....typo on the FindNext, should be a min() instead of max(), i.e. SELECT * FROM some_table WHERE id = ( SELECT min(rowid) FROM ( SELECT * FROM some_table WHERE original_expression AND new_expression AND rowid > X ) );
...typing has never been my strong point :-) On Fri, May 24, 2013 at 12:47 PM, Michael Falconer < michael.j.falco...@gmail.com> wrote: > 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. > -- Regards, Michael.j.Falconer. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
