On 2016/11/13 7:29 PM, Mike King wrote:
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.
So if I understand you correct - You use the temporary table with yet again a rowid of it's own and populating it using an ordered select from your main table, then using this table's rowid to find the list position of the item so that you can check its position in the select to know where to scroll to so that the first ID shown is the ID that was last navigated to. Right?
If so, the best way of doing all this is in your own code. It is some magnitudes faster than what you do here. If for some reason you can't do it in code, then your temp table is the best solution because nothing else can give you row numbers (without some really slow self-joins) and there is no guarantees made by any part of SQLite (or SQL in general) that a query will pop out a specific row order unless dictated by an ORDER BY clause and that happens only AFTER the query rows are produced in whatever arbitrary order (so AFTER any row-numbering-scheme could have been queried-in).
Perhaps one piece of advice I can offer is that setting the temp-table schemata to be created IN MEMORY rather than on disk might speed things up a lot (but if you need transactional or ACID integrity maintained you will need to switch it to disk again for normal querying, or use a different read-only connection with it set to MEMORY for this).
Hope that helps. Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
