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

This comment has been minimized.

Copy link

dalethestirling commented Jun 19, 2013

Just what I was looking for thanks for sharing

@aemxn

This comment has been minimized.

Copy link

aemxn commented Jul 22, 2016

Helpful 7 years old script!

@tciuro

This comment has been minimized.

Copy link

tciuro commented May 14, 2017

Good scripts never die ;-)

@nikialeksey

This comment has been minimized.

Copy link

nikialeksey commented Jul 11, 2018

Thanks!

@liuzhenyulive

This comment has been minimized.

Copy link

liuzhenyulive commented May 8, 2019

Thank you

@ahoefling

This comment has been minimized.

Copy link

ahoefling 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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.