Chapter 3

Chapter 3 — Sorting and Limiting

Without an ORDER BY clause, MySQL can return rows in any order it pleases — and that order may change between queries as the table grows or indexes are updated. This chapter covers how to sort results predictably, cap the number of rows returned, and page through large result sets efficiently.

1. The Full SELECT Clause Order

Before diving in, here's how all the clauses we've covered so far — plus the new ones in this chapter — fit together. They must appear in this order:

SELECT

Which columns (and expressions) to return

FROM

Which table to read from

WHERE

Filter rows — only matching rows proceed (Chapter 2)

ORDER BY

Sort the result rows — new this chapter

LIMIT

Cap the number of rows returned — new this chapter

OFFSET

Skip the first N rows — new this chapter

Clause order is enforced by MySQL. You can omit optional clauses, but you cannot reorder them. Writing LIMIT before WHERE, for example, is a syntax error. Later chapters add GROUP BY and HAVING between WHERE and ORDER BY.

2. ORDER BY — Sorting Results

Basic ascending sort

ORDER BY followed by a column name sorts the results by that column. The default direction is ascending (A→Z for text, low→high for numbers, oldest→newest for dates):

SELECT title, price FROM books ORDER BY price;

title	price
The Humans	7.99
The Midnight Library	8.99
Dune	9.99
Sapiens	10.99
Atomic Habits	11.99

Cheapest first — ascending order is the default.

ASC and DESC

Add ASC or DESC after the column name to be explicit about direction. ASC is the default; DESC reverses it:

-- Most expensive first SELECT title, price FROM books ORDER BY price DESC;

title	price
Atomic Habits	11.99
Sapiens	10.99
Dune	9.99
The Midnight Library	8.99
The Humans	7.99

-- Alphabetical by title (A → Z) SELECT title, genre FROM books ORDER BY title ASC;

title	genre
Atomic Habits	Self-help
Dune	Sci-Fi
Sapiens	Non-fiction
The Humans	Fiction
The Midnight Library	Fiction

Sorting by multiple columns

You can sort by more than one column — the second sort kicks in when two rows have the same value in the first column. Each column can have its own direction:

-- Sort by genre A→Z, then within each genre by price low→high SELECT title, genre, price FROM books ORDER BY genre ASC, price ASC;

title	genre	price
The Humans	Fiction	7.99
The Midnight Library	Fiction	8.99
Sapiens	Non-fiction	10.99
Dune	Sci-Fi	9.99
Atomic Habits	Self-help	11.99

The two Fiction books are sorted by price within their genre group.

Sorting by an expression or alias

You can sort by a calculated expression, or reference a column alias defined in the SELECT list:

-- Sort by the calculated stock value, highest first SELECT title, price, stock, price * stock AS stock_value FROM books ORDER BY stock_value DESC;

title	price	stock	stock_value
Atomic Habits	11.99	60	719.40
The Midnight Library	8.99	42	377.58
Sapiens	10.99	31	340.69
The Humans	7.99	25	199.75
Dune	9.99	18	179.82

ORDER BY with WHERE

ORDER BY always comes after WHERE. MySQL first filters the rows, then sorts only the rows that survived the filter:

-- Cheapest Fiction books first SELECT title, price FROM books WHERE genre = 'Fiction' ORDER BY price ASC;

title	price
The Humans	7.99
The Midnight Library	8.99

NULL values in ORDER BY

When a column contains NULL values, MySQL sorts them as if they are lower than any other value — NULLs appear first in ASC order and last in DESC order:

ORDER BY birth_year ASC

NULLrow 1

NULLrow 2

1920row 3

1931row 4

1976row 5

ORDER BY birth_year DESC

1976row 1

1931row 2

1920row 3

NULLrow 4

NULLrow 5

Push NULLs to the end in ASC order. If you want NULLs last regardless of sort direction, use IS NULL as a secondary sort: ORDER BY birth_year IS NULL, birth_year ASC. The IS NULL expression evaluates to 0 (false) for real values and 1 (true) for NULLs — so real values sort first.

3. LIMIT — Capping the Result

LIMIT N tells MySQL to return at most N rows, even if more rows match the query. It's always applied after filtering and sorting.

-- The three cheapest books SELECT title, price FROM books ORDER BY price ASC LIMIT 3;

title	price
The Humans	7.99
The Midnight Library	8.99
Dune	9.99
Sapiens	10.99 ← not returned
Atomic Habits	11.99 ← not returned

Only the first 3 rows after sorting are returned.

LIMIT without ORDER BY is non-deterministic. If you write LIMIT 3 without ORDER BY, MySQL returns whichever 3 rows it happens to find first — and that can change as the table is updated. Always pair LIMIT with ORDER BY so the result is predictable.

Common LIMIT patterns

-- The single most expensive book SELECT title, price FROM books ORDER BY price DESC LIMIT 1; Atomic Habits | 11.99 -- The most recently published book SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 1; The Midnight Library | 2020 -- Top 5 customers by number of orders (preview — uses COUNT, covered in Ch.8) SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC LIMIT 5;

4. OFFSET — Skipping Rows

OFFSET N tells MySQL to skip the first N rows before starting to return results. Combined with LIMIT, this is how you page through a large result set — showing 10 rows per page, for example.

-- All books sorted by price, showing rows 4 and 5 (skip the first 3) SELECT title, price FROM books ORDER BY price ASC LIMIT 2 OFFSET 3;

title	price
Sapiens	10.99
Atomic Habits	11.99

Visualising LIMIT and OFFSET

Imagine all 5 books sorted by price. OFFSET skips rows from the left; LIMIT takes only the next N:

row 1£7.99

row 2£8.99

row 3£9.99

row 4£10.99

row 5£11.99

Grey = skipped by OFFSET 3 · Blue = returned by LIMIT 2

The pagination formula

To show page P of results, with N rows per page:

-- Page 1: LIMIT N OFFSET 0 (skip 0) -- Page 2: LIMIT N OFFSET N (skip 1 page) -- Page 3: LIMIT N OFFSET 2*N (skip 2 pages) -- Page P: LIMIT N OFFSET (P-1)*N -- Showing page 2 of books (5 per page, sorted by title) SELECT title, price FROM books ORDER BY title ASC LIMIT 5 OFFSET 5; -- page 2: (2-1) * 5 = 5

Alternative syntax: LIMIT offset, count

MySQL also accepts a two-argument form of LIMIT where the first number is the offset and the second is the count. Both are equivalent:

-- These two queries are identical: LIMIT 2 OFFSET 3 LIMIT 3, 2 -- offset first, then count (easy to mix up!)

The two-argument form is easy to mix up. LIMIT 3, 2 means "skip 3, take 2" — not "take 3, skip 2". The explicit LIMIT 2 OFFSET 3 form is harder to misread and is preferred for clarity, especially when reading someone else's code.

Counting total rows for pagination

When building paginated results in an application, you typically need to know the total row count so you can calculate the number of pages. Run a separate COUNT(*) query without LIMIT:

-- How many Fiction books are there in total? SELECT COUNT(*) AS total FROM books WHERE genre = 'Fiction'; +-------+ | total | +-------+ | 2 | +-------+ -- Then fetch page 1 (5 per page) SELECT title, price FROM books WHERE genre = 'Fiction' ORDER BY title LIMIT 5 OFFSET 0;

OFFSET pagination gets slow on large tables. OFFSET 10000 forces MySQL to read and discard 10,000 rows before returning results — even though you only want 10. On tables with hundreds of thousands of rows this becomes a real performance problem. The solution (cursor-based pagination using

WHERE id >
    last_seen_id

) is an advanced topic, but worth knowing about if you build applications with large data sets.

5. Putting It All Together

Here's a realistic bookshop query that uses everything from the last three chapters — filtering, sorting, and limiting in one statement:

-- Show the top 3 best-value in-stock books (sorted by price ascending), -- excluding the Self-help genre, published in 2010 or later SELECT title, genre, published_year, price AS price_gbp, stock AS units_left FROM books WHERE genre != 'Self-help' AND published_year >= 2010 AND stock > 0 ORDER BY price ASC LIMIT 3;

title	genre	published_year	price_gbp	units_left
The Midnight Library	Fiction	2020	8.99	42
Sapiens	Non-fiction	2011	10.99	31

Only 2 rows returned — LIMIT 3 asked for up to 3, but only 2 rows matched all the WHERE conditions.

Chapter Summary

Syntax	What it does
ORDER BY col	Sort results by col ascending (A→Z, low→high). Default direction.
ORDER BY col DESC	Sort descending (Z→A, high→low).
ORDER BY col1, col2	Sort by col1 first; use col2 to break ties. Each column can have its own ASC/DESC.
ORDER BY alias	Sort by a column alias defined in the SELECT list.
NULLs in ORDER BY	ASC: NULLs first. DESC: NULLs last. Use `ORDER BY col IS NULL, col` to force NULLs last in ASC.
LIMIT N	Return at most N rows. Always pair with ORDER BY for predictable results.
LIMIT N OFFSET M	Skip M rows, then return the next N. Used for pagination.
LIMIT M, N	Alternative syntax for the same thing — offset first, count second. Prefer the OFFSET keyword form for readability.
Pagination formula	Page P with N rows/page: `LIMIT N OFFSET (P-1)*N`.

Next: Chapter 4 — Data Types and NULL. Columns are typed — INT, VARCHAR, DATE, DECIMAL, TEXT — and the choice matters for storage, performance, and correctness. We'll also go deeper on NULL: how it propagates through expressions, and how COALESCE and IFNULL help you handle missing values gracefully.