Description
-----------

Use the ORDER BY clause to order a resultset, such as that are returned from a
SELECT statement. You can specify just a column or use any expression with
functions. If you are using the GROUP BY clause, you can use grouping
functions in ORDER BY. Ordering is done after grouping.

You can use multiple ordering expressions, separated by commas. Rows will be
sorted by the first expression, then by the second expression if they have the
same value for the first, and so on.

You can use the keywords ASC and DESC after each ordering expression to force
that ordering to be ascending or descending, respectively. Ordering is
ascending by default.

You can also use a single integer as the ordering expression. If you use an
integer n, the results will be ordered by the nth column in the select
expression.

When string values are compared, they are compared as if by the STRCMP
function. STRCMP ignores trailing whitespace and may normalize characters and
ignore case, depending on the collation in use.

Duplicated entries in the ORDER BY clause are removed.

ORDER BY can also be used to order the activities of a DELETE or UPDATE
statement (usually with the LIMIT clause).

MariaDB starting with 10.3.2
----------------------------
Until MariaDB 10.3.1, it was not possible to use ORDER BY (or LIMIT) in a
multi-table UPDATE statement. This restriction was lifted in MariaDB 10.3.2.

MariaDB starting with 10.5
--------------------------
From MariaDB 10.5, MariaDB allows packed sort keys and values of non-sorted
fields in the sort buffer. This can make filesort temporary files much smaller
when VARCHAR, CHAR or BLOBs are used, notably speeding up some ORDER BY sorts.

Examples
--------

CREATE TABLE seq (i INT, x VARCHAR(1));
INSERT INTO seq VALUES (1,'a'), (2,'b'), (3,'b'), (4,'f'), (5,'e');

SELECT * FROM seq ORDER BY i;
+------+------+
| i    | x    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | b    |
|    4 | f    |
|    5 | e    |
+------+------+

SELECT * FROM seq ORDER BY i DESC;
+------+------+
| i    | x    |
+------+------+
|    5 | e    |
|    4 | f    |
|    3 | b    |
|    2 | b    |
|    1 | a    |
+------+------+

SELECT * FROM seq ORDER BY x,i;
+------+------+
| i    | x    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | b    |
|    5 | e    |
|    4 | f    |
+------+------+

ORDER BY in an UPDATE statement, in conjunction with LIMIT:

UPDATE seq SET x='z' WHERE x='b' ORDER BY i DESC LIMIT 1;

SELECT * FROM seq;
+------+------+
| i    | x    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | z    |
|    4 | f    |
|    5 | e    |
+------+------+

From MariaDB 10.3.2, ORDER BY can be used in a multi-table update:

CREATE TABLE warehouse (product_id INT, qty INT);
INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);

CREATE TABLE store (product_id INT, qty INT);
INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);

UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty =
store.qty+2 
 WHERE (warehouse.product_id = store.product_id AND store.product_id  >= 1)
  ORDER BY store.product_id DESC LIMIT 2;

SELECT * FROM warehouse;
+------------+------+
| product_id | qty  |
+------------+------+
|          1 |  100 |
|          2 |  100 |
|          3 |   98 |
|          4 |   98 |
+------------+------+

SELECT * FROM store;
+------------+------+
| product_id | qty  |
+------------+------+
|          1 |    5 |
|          2 |    5 |
|          3 |    7 |
|          4 |    7 |
+------------+------+

URL: https://mariadb.com/kb/en/order-by/