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 )

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. Required fields are marked *

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.       Also, please be aware that non-constructive comments will have their URL field erased before being approved in order to combat SEO spam.