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

I don't understand how this works? Where's the page you want to fetch from?

@ssokolow
Copy link
Author

I don't understand how this works? Where's the page you want to fetch from?

It's saying "skip the first 50 results, then show 10", so that LIMIT 50 means "page 6" because you're skipping "50 results divided by 10 results per page".

@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