Yes spot on.I did come up with a hybrid prototype which used a binary chop to work out what to read from the database and offset / limit to read the pages. This was slower than the temp table.
To muddy the waters a bit thecquery is keyed in by the user so to calculate the page in code I'd have to effectively do the same query in code surely? Thanks again Mike On Sun, 13 Nov 2016 at 18:55, R Smith <rsm...@rsweb.co.za> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
