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.
While doing that, I also ran across jPaginate (demo), a jQuery pagination plugin that, as long as you provide a gracefully-degrading fallback, is probably my new favorite design for a page selector.