On Fri, Mar 14, 2008 at 09:08:51PM -0400, dcharno scratched on the wall: > [EMAIL PROTECTED] wrote: > > This issue keeps coming up so I did a wiki page. > > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > I'm using the method described in the wiki and it was working pretty > well until I hit a data set where the sorting column was not unique. > > Here is the query from the wiki: > > SELECT title FROM tracks > WHERE singer='Madonna' > AND title<:firsttitle > ORDER BY title DESC > LIMIT 5; > > Imagine if several songs have the same title -- this could happen, for > example, if the ID3 tags are messed up ('untitled', 'untiled') or if the > user has several versions of the same song. Using this query, we'll end > up skipping over all the songs with the same title. > > Any thoughts on how to handle this?
You quoted the backward example, but I'm going to use the forward version. In addition to the "last seen title", remember the RowID for every row with the same "last seen title". For the forward query use "AND title>=:firsttitle" and pitch rows that match any of the remembered RowIDs. (Backwards use "<=".) You'll also need to increase your LIMIT by the number of RowIDs you remembered. If you don't end up pitching rows and get a full set of data before the LIMIT is hit, you can just call _finalize on the statement. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
