The real problem is going to be the breakpoint and ordering. FindFirst is simply a re-execution of the query. FindLast is the same, with inverted ordering. FindNext is just stepping (or issuing the same query but adding a condition to start after the breakboint). FindPrevious is simply the same query again, in inverted order, for the subset before the breakpoint).
For example if your query is something like: Select Lastname, Firstname from Names order by LastName, FirstName; Then assuming your row X is Lastname='Cricket' and Firstname='Jiminy' then: FindFirst is select lastname, firstname from names where (conditions) order by lastname, firstname limit 1; FindLast is select lastname, firstname from names where (conditions) order by lastname desc, firstname desc limit 1; FindNext is select lastname, firstname from names where (conditions) and lastname>='Cricket' and firstname>='Jiminy' order by lastname, firstname limit 1,1; FindPrev is select lastname, firstname from names where (conditions) and lastname<='Cricket' and firstname<='Jiminy' order by lastname desc, firstname desc limit 1,1; You can of course work with bounded subsets by getting the X and Y row keys and using BETWEEN on the unique ordered row keys to select the subset to which the (condition) should be applied. You will need approriate indexes and you will need to make sure that the order by (break) columns are constrained unique. SQL databases are not navigational record stores but are rather set algebra based. You can emulate a navigational recordstore using a relational database, but it will be somewhat inefficient no matter what you do ... Whatever you do, you must always specify an order by, otherwise you should assume that the rows will be returned in a random order which changes with each SELECT (this is especially true since you will need lots of judiciously chosen indexes to achieve any sort of performance whatsoever for any non-trivial table size -- there is nothing worse that someone designing and testing something with a database with 7 rows in it. Assume millions of rows. If it works for millions of rows acceptably then it will work just as well with 7 rows -- but the opposite is not true. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Michael Falconer > Sent: Thursday, 23 May, 2013 20:47 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Best way to implement Find commands > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
