Lesson 3: ORDER BY, LIMIT & Pagination

ORDER BY controls result order; LIMIT/OFFSET fetch a page at a time — the backbone of any 'show 20 results per page' feature.

Real-time example

A products page shows the 20 cheapest items first, then lets the user click 'next page' for the next 20.

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 20 OFFSET 0;   -- page 1
-- LIMIT 20 OFFSET 20;  -- page 2

What's happening

OFFSET skips rows before returning LIMIT rows — simple, but on huge tables it gets slower as the offset grows.

For deep pagination on large tables, use 'keyset pagination' (WHERE id > last_seen_id) instead of OFFSET for consistent speed.