SQL Pagination Without OFFSET

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.

-- Reasonably efficient pagination without OFFSET
-- SQLite version (Adapted from MS SQL syntax)
-- Source: http://www.phpbuilder.com/board/showpost.php?p=10376515&postcount=6

SELECT foo, bar, baz, quux FROM table
 WHERE oid NOT IN ( SELECT oid FROM table
                    ORDER BY title ASC LIMIT 50 )
 ORDER BY title ASC LIMIT 10

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.

Update: This site is also useful.

SQL Pagination Without OFFSET by Stephan Sokolow is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.