ORDER BY sorts the result rows; LIMIT (with OFFSET) restricts how many rows are returned. Together they handle sorting and pagination — essential for displaying ordered, paged data.
name, age users age;
name, age users age ;
users country, name;
users created_at ;
ORDER BY sorts by one or more columns, ascending (default) or DESC. Multiple columns provide tie-breaking (sort by the first, then the second for equal values).
SELECT * FROM users ORDER BY name LIMIT 10; -- the first 10 rows
SELECT * FROM users ORDER BY name LIMIT 10 OFFSET 20; -- skip 20, take 10 (page 3)
-- (syntax varies: MySQL/Postgres use LIMIT/OFFSET; SQL Server uses TOP / OFFSET-FETCH)
LIMIT n returns at most n rows; OFFSET m skips the first m. Together they implement offset pagination (page N = LIMIT pageSize OFFSET (N-1)*pageSize).
-- ❌ LIMIT without ORDER BY → row order is UNDEFINED (inconsistent pages!)
SELECT * FROM users LIMIT 10;
-- ✅ always ORDER BY a stable column for consistent pagination
SELECT * FROM users ORDER BY id LIMIT 10;
Crucial: without ORDER BY, the database may return rows in any order, so pages can overlap or skip rows. Always sort by a stable column when paginating.
⚠️ LIMIT 10 OFFSET 1000000 — the DB must SKIP a million rows first (slow on deep pages).
For large datasets, KEYSET (cursor) pagination is far more efficient:
WHERE id > :last_seen_id ORDER BY id LIMIT 10 -- uses an index, no skipping
ORDER BY and LIMIT are fundamental for displaying data in a useful, manageable way — sorting results and returning pages rather than entire tables is a near-universal need (any list, table, or feed in an application), so understanding them is essential everyday knowledge. ORDER BY (sorting by one or more columns, with tie-breaking) is necessary for presenting data in a meaningful order, and LIMIT/OFFSET implement pagination (returning manageable pages of data) — both ubiquitous in applications.
Two practical points are particularly important: always use ORDER BY when paginating (without it, row order is undefined, causing inconsistent pages that overlap or skip rows — a real, common bug), and understanding that OFFSET pagination degrades on deep pages (the database must skip all preceding rows, getting slow for large offsets), so keyset/cursor pagination (WHERE id > last_seen) is far more efficient for large datasets — valuable knowledge for performance.
Since sorting and pagination are required for virtually any data display, and since the consistency requirement (ORDER BY for stable pagination) and the performance consideration (OFFSET vs keyset for large data) directly affect correctness and speed, mastering ORDER BY and LIMIT — sorting, offset pagination, the stable-ordering requirement, and the keyset alternative for scale — is core, frequently-applied knowledge for building applications that present data effectively.