ORDER BY and LIMIT

~10 min · sql, order-by, limit, pagination

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Sorted, capped, paginated

By default the row order returned by SELECT is undefined. If order matters, you must say so with ORDER BY. Combined with LIMIT and OFFSET, this is also how you paginate.

ORDER BY col [ASC|DESC] — primary sort.
Multiple sort keys: ORDER BY brand ASC, price DESC.
Order by an expression: ORDER BY length(title) DESC.
LIMIT N OFFSET M — return N rows starting at row M.
LIMIT M, N — older shorthand; same as LIMIT N OFFSET M.

Warning: OFFSET is convenient but slow on large tables — SQLite must compute and discard the first M rows. For deep pagination, use keyset pagination: WHERE id < last_seen_id ORDER BY id DESC LIMIT 50. Constant time regardless of how deep you've paged.

Code

Sorting and paginating·sql

SELECT id, title, created_at
FROM   conversations
WHERE  archived = 0
ORDER  BY created_at DESC
LIMIT  20;

-- Page 3 of size 20 (offset-based — fine for small N)
SELECT id, title FROM conversations
ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Keyset pagination (fast at any depth)
SELECT id, title FROM conversations
WHERE created_at < ?           -- the created_at of the last row of the previous page
ORDER BY created_at DESC
LIMIT 20;

External links

Exercise

On a 100,000-row table, write the same query in two ways: (1) OFFSET-based pagination at page 1000 (offset 19,980), (2) keyset pagination using the previous page's last id. Use .timer on to compare. Then explain in your notes why keyset is so much faster.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousLIKE, GLOB, and Pattern Matching Next →DISTINCT and Aggregate Functions

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.