Sorting & Limiting
ORDER BY, LIMIT, OFFSET, and pagination
Skills taught: sql.order-by, sql.limit, sql.offset, sql.nulls-ordering
ORDER BY
-- Sort ascending (default, A to Z, 0 to 9)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC; -- same
-- Sort descending (Z to A, 9 to 0)
SELECT * FROM products ORDER BY price DESC;
-- Sort by text column alphabetically
SELECT * FROM users ORDER BY name ASC;
-- Multiple sort keys
-- First by category A-Z, then by price highest first
SELECT * FROM products
ORDER BY category ASC, price DESC;
-- Sort by an alias
SELECT name, price * stock AS inventory_value
FROM products
ORDER BY inventory_value DESC;
LIMIT and OFFSET
-- Return only the top 3 cheapest products
SELECT * FROM products ORDER BY price LIMIT 3;
-- The most expensive product
SELECT * FROM products ORDER BY price DESC LIMIT 1;
-- Pagination: page size = 2
-- Page 1: first 2 rows
SELECT * FROM products ORDER BY id LIMIT 2 OFFSET 0;
-- Page 2: skip 2, get next 2
SELECT * FROM products ORDER BY id LIMIT 2 OFFSET 2;
-- Page 3: skip 4, get next 2
SELECT * FROM products ORDER BY id LIMIT 2 OFFSET 4;
-- Formula: OFFSET = (page_number - 1) * page_size
Frequently Asked Questions
Does the row order matter without ORDER BY?
Yes — without ORDER BY, the database can return rows in any order it wants. It might be consistent today but change after an index rebuild or table update. Never depend on row order without an explicit ORDER BY.
What is wrong with OFFSET for deep pagination?
OFFSET 100000 still causes the database to process 100,000 rows before skipping them — it's O(n) for the offset. Keyset pagination (WHERE id > last_id ORDER BY id LIMIT 10) uses the index and is always O(log n). Use keyset for large datasets.