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

Reply via email to