0
Challenge · 10SQL I: Asking Questions of Data
ORDER BY, LIMIT & keyset pagination
SQLused in ~95% of apps
The DevLog homepage is getting two controls: a sort dropdown (Newest / Most liked) and a "Load more" button. Both feel simple — ORDER BY and LIMIT. But the third piece, how you fetch the *next* page, is where almost every app's first version ships a bug: a user scrolling the feed while someone else posts sees the same post twice, or misses one entirely. That is a pagination design choice, not a bug you can patch with more testing.
Core idea. ORDER BY sorts the finished result — it runs last in the query pipeline, so it can use SELECT aliases — and ties on the sort column need an explicit tiebreaker or their relative order is not guaranteed. LIMIT caps how many rows come back, but LIMIT without ORDER BY has no defined "first N" to give you. And for "load more," OFFSET (skip N rows) is simple but counts *positions*, so it silently drifts when rows are inserted between page loads; keyset pagination anchors to the *last row you actually saw* instead, which a concurrent insert cannot disturb.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
PAGINATION MODE
SHAREABLE URL
pulse.app/feed?page=1
jumpable + bookmarkableSELECT * FROM posts ORDER BY id DESC LIMIT 4 OFFSET 0
Post 16#16
Post 15#15
Post 14#14
Post 13#13
Try the race: go to page 2, click "New post arrives", then Next. Watch what offset does that cursor does not.
The page lives in the URL (?page=1) — reload or share it and the same view returns. Offset gives jumpable page numbers; cursor gives stable, scalable next/prev.
↑ play a little first, then dig in