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