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.

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

This entry was posted in Web Wandering & Opinion. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

By submitting a comment here you grant this site a perpetual license to reproduce your words and name/web site in attribution under the same terms as the associated post.

All comments are moderated. If your comment is generic enough to apply to any post, it will be assumed to be spam. Borderline comments will have their URL field erased before being approved.