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
