An out-of-the-box question: why do you need to have pages at all? I as a user never cared about the exact page a result was on.
Take a look at http://use-the-index-luke.com/no-offset which explains how to do keyset pagination. You can also get the total amount of results until a given row by doing the COUNT(*) query mentioned in this thread, which is pretty much how the keyset pagination works, but with the condition reversed. See my comment on how to structure the clause http://use-the-index-luke.com/no-offset#comment-2994786118 Wout. On Mon, Nov 14, 2016 at 8:23 AM Mike King <making1...@gmail.com> wrote: > Thanks. In answer to your question a page of results is displayed in a grid > on the screen. The user selects a row. If they then change the sort order I > want to show the page that contains the selected row (and reselect it). > Each row has a unique ID and i need to know the position in the result set > so I can calculate the page. > > Cheers > > On Sun, 13 Nov 2016 at 23:26, Igor Tandetnik <i...@tandetnik.org> wrote: > > > On 11/13/2016 6:17 PM, Mike King wrote: > > > Sorry to reply again so soon. I'm just playing about with your query. > The > > > values in Value1 and Value2 are not unique so I don't think your method > > > would work. > > > > Well, in this case, your problem is under-specified. How do you plan to > > assign a number to a row that's part of a group of rows all sharing the > > same Value2? > > > > One possible approach is to use ID to break ties: > > > > select count(*) from Test t1 join Test t2 > > where t2.ID = 1 and > > (t1.Value2 < t2.Value2 or (t1.Value2 = t2.Value2 and t1.ID <= t2.ID)); > > > > That's equivalent to "order by Value2, ID" in your temporary table > > approach. > > -- > > Igor Tandetnik > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
