Skip to content

Instantly share code, notes, and snippets.

@ssokolow
Created December 23, 2009 13:02
Show Gist options
  • Save ssokolow/262503 to your computer and use it in GitHub Desktop.
Save ssokolow/262503 to your computer and use it in GitHub Desktop.
Reasonably efficient pagination without OFFSET (SQLite version)
-- 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
@NSExceptional
Copy link

Oh I get it, it's semantically the same thing as OFFSET without using OFFSET. I was looking for something like this, which is what I ended up using:

SELECT * FROM Thingies t
    WHERE t.date > :inputDate
    ORDER BY t.date ASC
    LIMIT 10

@ssokolow
Copy link
Author

Yeah. It's basically a workaround for some databases' query planners not being very smart about OFFSET.

@atejada
Copy link

atejada commented Aug 22, 2022

Gotta admit...this is way cool 😎

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment