MySQL ORDER BY: Sort Results Ascending, Descending

Without ORDER BY, MySQL returns rows in no guaranteed order. It usually looks stable, until the day an index is added or the data grows and the order changes. If order matters, say so.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

Ascending and descending

query.sql

SQL

1
2

SELECT name, price FROM products ORDER BY price;
SELECT name, price FROM products ORDER BY price DESC;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Gel Pen       |  15.50 |
| Sticky Notes  |  35.00 |
| Notebook      |  80.00 |
| Marker Set    | 145.00 |
| Water Bottle  | 220.00 |
| Laptop Sleeve | 450.00 |
| Desk Lamp     | 650.00 |
| Backpack      | 999.00 |
+---------------+--------+
8 rows in set

+---------------+--------+
| name          | price  |
+---------------+--------+
| Backpack      | 999.00 |
| Desk Lamp     | 650.00 |
| Laptop Sleeve | 450.00 |
| Water Bottle  | 220.00 |
| Marker Set    | 145.00 |
| Notebook      |  80.00 |
| Sticky Notes  |  35.00 |
| Gel Pen       |  15.50 |
+---------------+--------+
8 rows in set

ASC (smallest first) is the default. DESC (largest first) is written explicitly.

Sorting by several columns

MySQL sorts by the first column, and uses the next only to break ties:

query.sql

SQL

SELECT category, name, price FROM products ORDER BY category ASC, price DESC;

Output

OUTPUT

+------------+---------------+--------+
| category   | name          | price  |
+------------+---------------+--------+
| bags       | Backpack      | 999.00 |
| bags       | Laptop Sleeve | 450.00 |
| home       | Desk Lamp     | 650.00 |
| home       | Water Bottle  | 220.00 |
| stationery | Marker Set    | 145.00 |
| stationery | Notebook      |  80.00 |
| stationery | Sticky Notes  |  35.00 |
| stationery | Gel Pen       |  15.50 |
+------------+---------------+--------+
8 rows in set

Products are grouped by category (alphabetically), and inside each category the most expensive comes first. Each column has its own direction.

Sort by an expression or an alias

query.sql

SQL

1
2

SELECT name, price * stock AS stock_value FROM products ORDER BY stock_value DESC LIMIT 3;
SELECT name, CHAR_LENGTH(name) AS name_length FROM products ORDER BY CHAR_LENGTH(name), name LIMIT 3;

Output

OUTPUT

+--------------+-------------+
| name         | stock_value |
+--------------+-------------+
| Desk Lamp    |    26000.00 |
| Backpack     |    24975.00 |
| Water Bottle |    16500.00 |
+--------------+-------------+
3 rows in set

+----------+-------------+
| name     | name_length |
+----------+-------------+
| Gel Pen  |           7 |
| Backpack |           8 |
| Notebook |           8 |
+----------+-------------+
3 rows in set

ORDER BY runs after SELECT, so it can use an alias (Order of Evaluation).

Sort by position

ORDER BY 2 means “the second column of the select list”. It works, but it is easy to break when someone edits the column list. Write the name.

query.sql

SQL

SELECT name, price FROM products ORDER BY 2 DESC LIMIT 2;

Output

OUTPUT

+-----------+--------+
| name      | price  |
+-----------+--------+
| Backpack  | 999.00 |
| Desk Lamp | 650.00 |
+-----------+--------+
2 rows in set

Always add a tie-breaker

If several rows have the same sort value, their relative order is not defined. This matters for pagination: a row may appear on two pages or on none. Add a unique column last:

query.sql

SQL

SELECT name, category FROM products ORDER BY category, id;

Output

OUTPUT

+---------------+------------+
| name          | category   |
+---------------+------------+
| Backpack      | bags       |
| Laptop Sleeve | bags       |
| Desk Lamp     | home       |
| Water Bottle  | home       |
| Notebook      | stationery |
| Gel Pen       | stationery |
| Marker Set    | stationery |
| Sticky Notes  | stationery |
+---------------+------------+
8 rows in set

id is unique, so the order is always the same.

Text sorting follows the collation

Text is sorted according to the column’s collation. With utf8mb4_0900_ai_ci, a, A and á sort together, and 10 sorts before 2 because it is text (Case-Sensitive Searches).

query.sql

SQL

1
2

SELECT label FROM (SELECT '10' AS label UNION ALL SELECT '9' UNION ALL SELECT '2') AS t ORDER BY label;
SELECT label FROM (SELECT '10' AS label UNION ALL SELECT '9' UNION ALL SELECT '2') AS t ORDER BY label + 0;

Output

OUTPUT

+-------+
| label |
+-------+
| 10    |
| 2     |
| 9     |
+-------+
3 rows in set

+-------+
| label |
+-------+
| 2     |
| 9     |
| 10    |
+-------+
3 rows in set

Numbers stored as text sort alphabetically. The fix is a real number column, or ORDER BY label + 0 in an emergency (slow, and it cannot use an index).

Sorting is not free

ORDER BY on a large result needs a sort, which uses memory and may spill to disk. An index that matches the order can avoid it (Sorting and Indexes).

warning

Relying on accidental order

A table that comes out sorted by id today does not promise it tomorrow. SELECT without ORDER BY is for situations where order truly does not matter.