Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 8 of 50

ORDER BY and LIMIT

Sort results with ORDER BY, sort by several columns, take the top rows with LIMIT and OFFSET, and build simple pagination, with verified sample output.

13 minReading dataUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonSort query results in a defined order and return only the first rows or a single page of rows.

Rows have no promised order

A table is a set of rows, not a list. When you run a SELECT without ORDER BY, MySQL returns the rows in whatever order is cheapest. In practice that often looks like the order you inserted them, which tempts people to rely on it. Do not. The order can change after an update, a new index or a server upgrade. If order matters, ask for it.

ORDER BY

ORDER BY goes near the end of the query and takes one or more columns:

sql
SELECT name, price FROM products ORDER BY price;
result
+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Water Bottle                |  12.00 |
| Resistance Bands            |  14.99 |
| Ceramic Mug Set             |  18.75 |
| Puzzle 1000 Pieces          |  19.99 |
| Wireless Mouse              |  24.99 |
| Yoga Mat                    |  27.50 |
| SQL Basics Handbook         |  29.95 |
| Desk Lamp                   |  32.00 |
| Web Design Patterns         |  34.50 |
| USB-C Hub                   |  39.00 |
| Data Structures Illustrated |  45.00 |
| Building Blocks Set         |  49.99 |
| Standing Desk Mat           |  55.00 |
| Mechanical Keyboard         |  89.50 |
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+--------+

The default direction is ascending, smallest first. Add DESC for descending. You can write ASC explicitly to be clear.

sql
SELECT name, price FROM products ORDER BY price DESC;
result
+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
| Mechanical Keyboard         |  89.50 |
| Standing Desk Mat           |  55.00 |
| Building Blocks Set         |  49.99 |
| Data Structures Illustrated |  45.00 |
| USB-C Hub                   |  39.00 |
| Web Design Patterns         |  34.50 |
| Desk Lamp                   |  32.00 |
| SQL Basics Handbook         |  29.95 |
| Yoga Mat                    |  27.50 |
| Wireless Mouse              |  24.99 |
| Puzzle 1000 Pieces          |  19.99 |
| Ceramic Mug Set             |  18.75 |
| Resistance Bands            |  14.99 |
| Water Bottle                |  12.00 |
+-----------------------------+--------+

Text sorts alphabetically (according to the column's collation), and dates sort from earliest to latest:

sql
SELECT first_name, last_name FROM customers ORDER BY last_name;
result
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Ben        | Carter    |
| Chloe      | Dubois    |
| Farid      | Khan      |
| Daniel     | Kim       |
| Jack       | Miller    |
| Kavya      | Nair      |
| Liam       | O'Brien   |
| Grace      | Okafor    |
| Elena      | Rossi     |
| Isabel     | Santos    |
| Hiro       | Tanaka    |
| Asha       | Verma     |
+------------+-----------+

Sorting by more than one column

When two rows have the same value in the first column, MySQL looks at the next one. List columns in order of importance, each with its own direction:

sql
SELECT category_id, name, price FROM products ORDER BY category_id ASC, price DESC;
result
+-------------+-----------------------------+--------+
| category_id | name                        | price  |
+-------------+-----------------------------+--------+
|           1 | Noise-Cancelling Headphones | 199.99 |
|           1 | Mechanical Keyboard         |  89.50 |
|           1 | USB-C Hub                   |  39.00 |
|           1 | Wireless Mouse              |  24.99 |
|           2 | Data Structures Illustrated |  45.00 |
|           2 | Web Design Patterns         |  34.50 |
|           2 | SQL Basics Handbook         |  29.95 |
|           3 | Standing Desk Mat           |  55.00 |
|           3 | Desk Lamp                   |  32.00 |
|           3 | Ceramic Mug Set             |  18.75 |
|           4 | Yoga Mat                    |  27.50 |
|           4 | Resistance Bands            |  14.99 |
|           4 | Water Bottle                |  12.00 |
|           5 | Building Blocks Set         |  49.99 |
|           5 | Puzzle 1000 Pieces          |  19.99 |
+-------------+-----------------------------+--------+

Products are grouped by category, and within each category the most expensive comes first. Adding a unique column such as id at the end guarantees that even completely equal rows come back in the same order each time.

Sorting by an expression or alias

You can sort by anything you can select. Unlike WHERE, ORDER BY runs after the select list, so aliases work:

sql
SELECT name, price * stock AS stock_value FROM products ORDER BY stock_value DESC LIMIT 5;
result
+-----------------------------+-------------+
| name                        | stock_value |
+-----------------------------+-------------+
| SQL Basics Handbook         |     5990.00 |
| Mechanical Keyboard         |     5370.00 |
| Noise-Cancelling Headphones |     4999.75 |
| Resistance Bands            |     4497.00 |
| Web Design Patterns         |     4140.00 |
+-----------------------------+-------------+

You can also refer to a column by its position (ORDER BY 2), but names are clearer and survive changes to the select list.

LIMIT: the top N

LIMIT n stops after n rows. With ORDER BY it answers "top N" questions:

sql
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
result
+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
| Mechanical Keyboard         |  89.50 |
| Standing Desk Mat           |  55.00 |
+-----------------------------+--------+
sql
SELECT first_name, signup_date FROM customers ORDER BY signup_date DESC LIMIT 1;
result
+------------+-------------+
| first_name | signup_date |
+------------+-------------+
| Liam       | 2023-09-12  |
+------------+-------------+

The second query finds the most recent customer. Without ORDER BY, LIMIT just returns some rows, which is only useful when you are peeking at a table.

OFFSET and pagination

To skip rows, add OFFSET. Together they build pages of results:

sql
SELECT id, name, price FROM products ORDER BY id LIMIT 5 OFFSET 0;
result
+----+-----------------------------+--------+
| id | name                        | price  |
+----+-----------------------------+--------+
|  1 | Wireless Mouse              |  24.99 |
|  2 | Mechanical Keyboard         |  89.50 |
|  3 | USB-C Hub                   |  39.00 |
|  4 | Noise-Cancelling Headphones | 199.99 |
|  5 | SQL Basics Handbook         |  29.95 |
+----+-----------------------------+--------+
sql
SELECT id, name, price FROM products ORDER BY id LIMIT 5 OFFSET 5;
result
+----+-----------------------------+-------+
| id | name                        | price |
+----+-----------------------------+-------+
|  6 | Web Design Patterns         | 34.50 |
|  7 | Data Structures Illustrated | 45.00 |
|  8 | Desk Lamp                   | 32.00 |
|  9 | Ceramic Mug Set             | 18.75 |
| 10 | Standing Desk Mat           | 55.00 |
+----+-----------------------------+-------+

With a page size of 5, page 1 starts at offset 0, page 2 at offset 5, page 3 at offset 10. In general the offset is (page - 1) * page_size. MySQL also accepts the shorter form LIMIT 5, 5 (offset first, then count), but that order is easy to misread, so prefer LIMIT 5 OFFSET 5.

Always sort when you paginate. Otherwise two pages can overlap or skip rows, because the server may pick a different order for each request.

Deep pages have a cost. OFFSET 100000 forces MySQL to read and throw away 100,000 rows before returning yours. For huge tables, developers use "keyset" pagination, such as WHERE id > 100000 ORDER BY id LIMIT 5, which can jump straight to the right place using an index.

Common mistakes

  • Relying on the default order. It is not a promise.
  • Putting LIMIT before ORDER BY. The order is fixed: ORDER BY first, then LIMIT.
  • Forgetting DESC and getting the smallest values when you wanted the largest.
  • Paginating without a unique tie-breaker, so rows appear twice or vanish between pages.
  • Using huge OFFSET values on big tables.

Exercise

Write queries that return: (1) the five cheapest products, (2) customers sorted by country and then by last name, and (3) the second page of customers (page size 4), sorted by id.

Show one possible answer
sql
SELECT name, price FROM products ORDER BY price ASC LIMIT 5;
SELECT country, last_name, first_name FROM customers ORDER BY country, last_name;
SELECT id, first_name FROM customers ORDER BY id LIMIT 4 OFFSET 4;
result
+--------------------+-------+
| name               | price |
+--------------------+-------+
| Water Bottle       | 12.00 |
| Resistance Bands   | 14.99 |
| Ceramic Mug Set    | 18.75 |
| Puzzle 1000 Pieces | 19.99 |
| Wireless Mouse     | 24.99 |
+--------------------+-------+
+-------------+-----------+------------+
| country     | last_name | first_name |
+-------------+-----------+------------+
| France      | Dubois    | Chloe      |
| India       | Khan      | Farid      |
| India       | Nair      | Kavya      |
| India       | Verma     | Asha       |
| Ireland     | O'Brien   | Liam       |
| Italy       | Rossi     | Elena      |
| Japan       | Tanaka    | Hiro       |
| Nigeria     | Okafor    | Grace      |
| Portugal    | Santos    | Isabel     |
| South Korea | Kim       | Daniel     |
| UK          | Carter    | Ben        |
| USA         | Miller    | Jack       |
+-------------+-----------+------------+
+----+------------+
| id | first_name |
+----+------------+
|  5 | Elena      |
|  6 | Farid      |
|  7 | Grace      |
|  8 | Hiro       |
+----+------------+

Key points

  • Without ORDER BY the order of rows is not guaranteed. If order matters, always ask for it.
  • ORDER BY column ASC (the default) sorts upwards and DESC sorts downwards. Add more columns to break ties.
  • LIMIT n returns the first n rows; LIMIT n OFFSET m skips m rows first. Use it with ORDER BY for top-N and pagination.
  • Large OFFSET values get slow because MySQL still reads the skipped rows.

Check yourself

1. Which query returns the three most expensive products?
2. What does LIMIT 5 OFFSET 10 return?
3. What is the default sort direction of ORDER BY?
4. Why add a second column to ORDER BY, such as ORDER BY price DESC, id?