I'm not 100% sure this is what you're asking for, but try this... CREATE TABLE foo(Ranking INTEGER PRIMARY Key, Col1, Col2, ... ); INSERT INTO foo SELECT ... ORDER BY ...; SELECT * FROM foo WHERE Ranking BETWEEN x AND y;
Warnings in the documentation aside, this will give you the rows in the table ordered the way you want, retrievable by position. Unless things have changed since I last used this trick, anyway. :P -T On Thu, Nov 6, 2008 at 12:09 AM, Ian Walters <[EMAIL PROTECTED]> wrote: > Hi, > > What I'm trying to do is create an ordered table in SQLite that I can > then look up by row number. If performance isn't considered this is > actually quite easy. For example > > CREATE TEMPORARY TABLE mytable AS ... complex select statement ordered > by label, recordId ....; > CREATE INDEX ON mytable (label, recordId); > > SELECT recordId, label FROM mytable ORDER BY label, recordId LIMIT 1 > OFFSET row; > > However I'm very concerned about performance. For instance, I don't > want to re prepare the query, so 'row' would need to be a bound value in > the above. Given that the C++ API for SQLite only has _step, another > worry is that is what OFFSET might do internally. O(N) lookup per row > isn't good enough. > > My other thought was to use ROWID, e.g. "WHERE ROWID = :row", but the > warnings in that documentation seem to indicate it isn't good enough to > use as a row number. > > Alternatively suggestions on how to add a row as an explicit column in > the select statement for the temporary table would also be useful. > Again, performance matters. It doesn't have to be as fast as the row > lookup, but anything involving 'count(*) <complex select>' isn't going > to cut it. Something like 'SELECT ..., rowNumber++ FROM <complex > select>', although I realize there is no ++ operator in SQLite. > > As a side question, if I order the select statement when creating the > table, will that order the inserts, or will that be ignored. If it > isn't ignored I may be able to do this via a sqlite3_create_function > > Thanks in advance for any help, > > -- > Ian > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
