I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text and Value2 - Text). After doing an order by in a select query I'd like to know the row number that contains a particular ID.
(The real world use is this: I have an application which displays paged lists of results. If you change the sort order I'd like the application to go to the page that contains the current selected ID. To do this I need to know what is the position in the sorted list of the ID). 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; I know SQL light doesn't support rownum (like Oracle) but is there any way I could simplify this using a CTE so I don't have to create the temp table? All I really want is the number of the row with the ID in the sorted list. Cheers, Reply Forward _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
