MySQL – ORDER BY

A result set has no order until you ask for one. ORDER BY is how you ask, and it is one of the last things MySQL does — after WHERE, after GROUP BY, after the select list has been evaluated.

ASC and DESC

Ascending is the default, so ORDER BY name and ORDER BY name ASC are the same thing. DESC reverses it:

SELECT name, display_order FROM product WHERE type = 'PIZZA' ORDER BY display_order DESC LIMIT 4;

+-----------------------+---------------+
| name                  | display_order |
+-----------------------+---------------+
| Buffalo Chicken Pizza |             8 |
| Hawaiian Pizza        |             7 |
| BBQ Chicken Pizza     |             6 |
| Veggie Lovers Pizza   |             5 |
+-----------------------+---------------+

Several columns

Each column gets its own direction, and later columns only break ties in earlier ones:

SELECT p.name, ps.size, ps.price
FROM   product_size ps JOIN product p ON p.id = ps.product_id
WHERE  p.type = 'DRINK'
ORDER  BY ps.price DESC, p.name ASC
LIMIT  5;

+--------------+-------+-------+
| name         | size  | price |
+--------------+-------+-------+
| Diet Pepsi   | LARGE |  2.99 |
| Iced Tea     | LARGE |  2.99 |
| Mountain Dew | LARGE |  2.99 |
| Pepsi        | LARGE |  2.99 |
| Starry       | LARGE |  2.99 |
+--------------+-------+-------+

Every drink is 2.99 at large, so price DESC decides nothing and name ASC decides everything. Writing ORDER BY price DESC, name — one DESC, one implicit ASC — is normal and not a mistake.

Sorting by an alias or an expression

By the time ORDER BY runs, the select list has been evaluated, so its aliases exist:

SELECT name, price * 1.0725 AS with_tax
FROM   product_size ps JOIN product p ON p.id = ps.product_id
ORDER  BY with_tax DESC
LIMIT  3;

That is why an alias works here and not in WHERE. You can also sort by an expression directly, or by a column you did not select at all — ORDER BY created_at is legal even when created_at is not in the output.

MySQL additionally allows ORDER BY 2, meaning "the second column of the output". Avoid it: it silently re-sorts by something else the moment anyone reorders the select list.

Where NULLs go

MySQL treats NULL as lower than every value, so it sorts first in ASC and last in DESC:

SELECT customer_name, address_line1
FROM   customer_order
ORDER  BY address_line1
LIMIT  5;

+----------------+---------------+
| customer_name  | address_line1 |
+----------------+---------------+
| Alex Rivera    | NULL          |
| Morgan Ellis   | NULL          |
| Dana Whitfield | NULL          |
| Jordan Blake   | NULL          |
| Demo Customer  | NULL          |
+----------------+---------------+

Those are the carryout orders, which have no delivery address. MySQL has no NULLS LAST clause — that is standard SQL it does not implement — so to push them to the other end, sort by a nullness flag first:

SELECT customer_name, address_line1
FROM   customer_order
ORDER  BY address_line1 IS NULL, address_line1
LIMIT  5;

address_line1 IS NULL is 0 for real addresses and 1 for NULLs, so ascending puts the NULLs last.

No ORDER BY means no order

This is the part worth taking seriously. A query without ORDER BY has no guaranteed order at all — not "insertion order", not "primary key order". MySQL returns rows in whatever order the plan it chose produced them.

It will very often look sorted, because a small table read through its clustered primary key comes back in key order. Then an index is added, or the table grows, or the optimizer picks a different plan, and the order changes with no code change at all. If order matters, say so.

The same applies to GROUP BY: MySQL 8 does not sort groups implicitly. If you want grouped output in a particular order, add ORDER BY.

Sorting is not free

If an index already provides the order, MySQL reads it and sorts nothing. If it does not, the rows go through a sort — Using filesort in EXPLAIN, which despite the name is usually in memory.

Two consequences worth knowing. An index only helps if the ORDER BY matches its column order, so ORDER BY a, b can use an index on (a, b) and not one on (b, a). And sorting by an expression — ORDER BY price * 1.0725 — can never use an index, for the same reason a function in WHERE cannot. See indexes.

What to remember

• ASC is the default; each column takes its own direction.
• Aliases work here because ORDER BY runs after SELECT.
• NULLs sort first ascending. Use ORDER BY col IS NULL, col to flip that.
• No ORDER BY, no guaranteed order — however sorted it happens to look.