Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@dalethestirling
Copy link

dalethestirling commented Jun 19, 2013

Just what I was looking for thanks for sharing

@aemxn
Copy link

aemxn commented Jul 22, 2016

Helpful 7 years old script!

@tciuro
Copy link

tciuro commented May 14, 2017

Good scripts never die ;-)

@nikialeksey
Copy link

nikialeksey commented Jul 11, 2018

Thanks!

@liuzhenyulive
Copy link

liuzhenyulive commented May 8, 2019

Thank you

@SkyeHoefling
Copy link

SkyeHoefling commented Jan 9, 2020

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

@brycecorbitt
Copy link

brycecorbitt commented Jul 2, 2020

Just what I needed! 11 years strong :)

@manufaktor
Copy link

manufaktor commented Aug 18, 2021

12 years and still useful. Thanks!

@NSExceptional
Copy link

NSExceptional commented Apr 17, 2022

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

@ssokolow
Copy link
Author

ssokolow commented Apr 17, 2022

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

NSExceptional commented Apr 18, 2022

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

ssokolow commented Apr 19, 2022

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

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