Analogous to the percentile solution (it's actually the same thing), you can use a checkpointing table. This has roughly the complexity of SQRT(n) for both read and write.
I.E. say you expect to have 1M records and define order based on value then id. You then make a checkpoint table (first_rank,value,rec_id) holding every 1000th record in sorted order. So row 1 of checkpoint table coresponds to the 1000th sorted record. When you insert/delete a row, you only need to update checkpoints that come after said row. When you are searching for row 4521, you do something like: SELECT FROM table JOIN checkpoint WHERE ( (table.value=checkpoint.value AND table.id>=checkpoint.id) OR table.value>checkpoint.value ) AND checkpoint.first_rank=4500 ORDER BY table.value ASC,table.id ASC LIMIT 21,1 -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list email@example.com http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users