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.