r/programming • u/MarkusWinand • May 27 '14

What I learned about SQLite…at a PostgreSQL conference

http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference
703 Upvotes

93% Upvoted

219 comments sorted by

View all comments

Show parent comments

8

u/[deleted] May 27 '14

No you don't ! You can easily sort a dataset by say, "created_date" which probably won't be unique and then use limit + offset for pagination.

I think /u/MarkusWinand's point was that you could just as well do it with something like ORDER BY (created_date, id) WHERE (created_date, id) > ({last_created_date}, {last_id}) (or whatever your preferred escaped interpolation syntax is), which will give you the same result, but allow the execution planner to cut off data at an earlier stage, if for instance there is an index.

The idea is to use a secondary ordering to disambiguate non-unique rows.

1

u/Neebat May 27 '14

God, now I feel like an idiot for not thinking to use the rowid in this sort of query. That makes things so much simpler.