Sorting and limiting results with ORDER BY and LIMIT

ORDER BY Controls Row Sequence

Without ORDER BY, row order is not guaranteed — the database returns rows in whatever order is fastest. Always specify ORDER BY if your app depends on a consistent order.

-- Cheapest first
SELECT name, price FROM products ORDER BY price ASC;

-- Most expensive first
SELECT name, price FROM products ORDER BY price DESC;

-- Sort by category, then by price within each category
SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

LIMIT Caps the Result Size

LIMIT prevents the database from sending thousands of rows you don't need. It runs last — after sorting — so you always get the top N rows of the sorted result:

-- Top 10 most expensive products
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 10;

Pagination with OFFSET

OFFSET skips the first N rows. Combined with LIMIT, it powers pagination:

-- Page 2, 10 items per page
SELECT name, price FROM products
ORDER BY name ASC
LIMIT 10 OFFSET 10;

OFFSET pagination is simple but slow on large datasets — for page 100 it still scans and discards 990 rows. Cursor-based pagination using a WHERE condition on the last seen ID is faster for deep pages.