Just thought I’d look up how people paginate efficiently in SQLite since, apparently, OFFSET is just an alias for “discard N first results”. The recommended solution is to use a WHERE clause to mimic offset using the same column index you use for sorting… but that’s only really possible for “Next” or “More…” links.
I ended up discovering that MS SQL, Firebird, Oracle, and DB2 also lack OFFSET and the solution (MS SQL version) is to grab a list of primary keys which should be skipped using a subquery. (because retrieving just primary keys is faster than retrieving and discarding all the columns you don’t need)
To make sure I didn’t forget, I threw up an SQLite version on GitHub Gists.
Update: This site is also useful.
This work, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Share Alike 3.0 Unported License.