Reasonably efficient pagination without OFFSET (SQLite version)

pagination_example.sql

-- 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

Good scripts never die ;-)

Thanks!

Thank you

My ORM wasn't generating fast reads with paging. This is a great query that I was able to use instead with under 1 second reads for datasets > 30,000 rows

Just what I needed! 11 years strong :)

12 years and still useful. Thanks!

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

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".

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

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

Gotta admit...this is way cool 😎