Hello Mike,
What I do is I note the currently selected ID prior to the sort, then sort using a query and return the ID's in the new sorted order. Then in the program I search for the ID and display the selected line again in my list control. Basically, I maintain in memory a complete list of the ID's in a vector in the currently selected order. The vector is also used when it's time to resolve because I have a 1:1 mapping between the index of the item and the ID of the item. The demand-load happens for the visible page so, when I navigate to the selected item, that page resolves against the DB. This only works because I control the program. It probably wouldn't work for a web page or something like that. Sunday, November 13, 2016, 12:29:12 PM, you wrote: MK> I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text MK> and Value2 - Text). After doing an order by in a select query I'd like to MK> know the row number that contains a particular ID. MK> (The real world use is this: I have an application which displays paged MK> lists of results. If you change the sort order I'd like the application to MK> go to the page that contains the current selected ID. To do this I need to MK> know what is the position in the sorted list of the ID). MK> So, after some experimentation, I'm using a temporary table to hold the MK> ordered IDs and then getting the rowid of the row with the ID I want. (in MK> this example the list is sorted by Value2 and the selected ID=1): MK> create temporary table TempIDs as select ID from Test order by Value2; MK> select rowid from TempIDs where ID = 1; MK> drop Table TempIDs; MK> I know SQL light doesn't support rownum (like Oracle) but is there any way MK> I could simplify this using a CTE so I don't have to create the temp table? MK> All I really want is the number of the row with the ID in the sorted list. MK> Cheers, MK> Reply MK> Forward MK> _______________________________________________ MK> sqlite-users mailing list MK> sqlite-users@mailinglists.sqlite.org MK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Teg mailto:t...@djii.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
