That is what I did, did my select into a temp table filtered and sorted just the way I wanted it and used the rowid since it would match the index in the list control.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nicolas Williams Sent: Saturday, January 10, 2009 4:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Getting the "position" (like LIMIT) for a query On Sat, Jan 10, 2009 at 04:33:19PM -0500, Igor Tandetnik wrote: > "Lukas Haase" <lukasha...@gmx.at> wrote in message > news:gkat07$n2...@ger.gmane.org > > "SELECT keyword FROM keywords ORDER BY keyword LIMIT %d, %d", iFrom, iTo-iFrom." > > > > I use an SQLite database to fill a virtual list control in Windows. > > In this control, I just tell the control the numer of my elements > > and the control tells me for which range data is needed. > > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor One thing I've done before is to use a rowid to track each "page" of results: SELECT rowid, keyword FROM keywords WHERE rowid > :pagestart ORDER BY keyword LIMIT :pagesize then remember the last rowid from the result set and use it to start the next result set. If you have a JOIN then you can use this trick for one table in the query, just pick it carefully. Paging backwards efficiently is not as easy. You want to come up with a query that can start at some rowid and scan _backwards_ through the table. This: SELECT rowid, keyword FROM keywords WHERE rowid < :pagestart ORDER BY keyword LIMIT :pagesize doesn't do what you want, of course (EXPLAIN will show that it uses the Next instruction instead of Prev). This: SELECT rowid, keyword FROM (SELECT rowid, keyword FROM keywords WHERE rowid < :pagestart ORDER BY rowid DESC LIMIT :pagesize) ORDER BY keyword; does use the Prev instruction, instead of Next, to scan the table in the sub-select, so it will process the fewest possible rows. Using rowid makes this very fast. I'm surprised that the wiki page for scrolling cursors doesn't mention this. Incidentally, I think many, if not all queries that have an outer table scan, and many, if not all joins that have an inner table scan but not an outer table scan, could be programmatically modified to create a scrolling cursor, forward and backwards. All that has to be done is: pick one table whose rowid to extract, add the where clause for rowid < or > than some variable, add the limit clause, and the sub-select for paging backwards. But parsing the select just do that is hard, while the SQLite3 parser is uniquely positioned to do it for the user. So perhaps there's a small RFE here? Cheers, Nico -- _______________________________________________ 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
