Wow, that's clever and obvious :) Taking this a stage further, the problem is that in the real app the user enters a query so there'll be a where clause to content with as well as the sort order. So, I think I'll use a CTE to build a list of the selected data and then use your query to select a record based on the sort criteria.
Thanks for your help On 13 November 2016 at 19:47, Igor Tandetnik <i...@tandetnik.org> wrote: > On 11/13/2016 12:29 PM, Mike King wrote: > >> So, after some experimentation, I'm using a temporary table to hold the >> ordered IDs and then getting the rowid of the row with the ID I want. (in >> this example the list is sorted by Value2 and the selected ID=1): >> >> create temporary table TempIDs as select ID from Test order by Value2; >> select rowid from TempIDs where ID = 1; >> drop Table TempIDs; >> > > select count(*) from Test > where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1); > > -- > 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
