# ORDER BY

# Contexts

The clauses in a SELECT have a specific order:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...
    ORDER BY ...  -- goes here
    LIMIT ... OFFSET ...;

( SELECT ... ) UNION ( SELECT ... ) ORDER BY ...  -- for ordering the result of the UNION.

SELECT ... GROUP_CONCAT(DISTINCT x ORDER BY ... SEPARATOR ...) ...

ALTER TABLE ... ORDER BY ... -- probably useful only for MyISAM; not for InnoDB

# Basic

ORDER BY x

x can be any datatype.

NULLs precede non-NULLs.
The default is ASC (lowest to highest)
Strings (VARCHAR, etc) are ordered according the COLLATION of the declaration
ENUMs are ordered by the declaration order of its strings.

# ASCending / DESCending

ORDER BY x ASC  -- same as default
ORDER BY x DESC  -- highest to lowest
ORDER BY lastname, firstname  -- typical name sorting; using two columns
ORDER BY submit_date DESC  -- latest first
ORDER BY submit_date DESC, id ASC  -- latest first, but fully specifying order.

ASC = ASCENDING, DESC = DESCENDING
NULLs come first even for DESC.
In the above examples, INDEX(x), INDEX(lastname, firstname), INDEX(submit_date) may significantly improve performance.

But... Mixing ASC and DESC, as in the last example, cannot use a composite index to benefit. Nor will INDEX(submit_date DESC, id ASC) help -- "DESC" is recognized syntactically in the INDEX declaration, but ignored.

# Some tricks

ORDER BY FIND_IN_SET(card_type, "MASTER-CARD,VISA,DISCOVER") -- sort 'MASTER-CARD' first.
ORDER BY x IS NULL, x  -- order by `x`, but put `NULLs` last.

Custom ordering

SELECT * FROM some_table WHERE id IN (118, 17, 113, 23, 72) 
ORDER BY FIELD(id, 118, 17, 113, 23, 72);

Returns the result in the specified order of ids.

id	...
118	...
17	...
113	...
23	...
72	...

Useful if the ids are already sorted and you just need to retrieve the rows.

← UPDATE Group By →