# ORDER BY ## Description Use the `ORDER BY` clause to order a resultset, such as that are returned from a [SELECT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md) 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 are 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 is ordered by the n^th column in the select expression. When string values are compared, they are compared as if by the [STRCMP](/docs/server/reference/sql-functions/string-functions/strcmp.md) function. `STRCMP` ignores trailing whitespace and may normalize characters and ignore case, depending on the [collation](/docs/server/reference/data-types/string-data-types/character-sets.md) in use. Duplicated entries in the `ORDER BY` clause are removed. `ORDER BY` can also be used to order the activities of a [DELETE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete.md) or [UPDATE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md) statement (usually with the [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md) clause). {% tabs %} {% tab title="Current" %} It is possible to use `ORDER BY` (or [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md)) in a multi-table [UPDATE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md) statement. {% endtab %} {% tab title="< 10.3.2" %} It is **not** possible to use `ORDER BY` (or [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md)) in a multi-table [UPDATE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md) statement. {% endtab %} {% endtabs %} {% tabs %} {% tab title="Current" %} 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 `BLOB` columns are used, notably speeding up some `ORDER BY` sorts. {% endtab %} {% tab title="< 10.5" %} MariaDB does not allow packed sort keys and values of non-sorted fields in the sort buffer. {% endtab %} {% endtabs %} ## Examples ```sql 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](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md) statement, in conjunction with [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md): ```sql 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 | +------+------+ ``` `ORDER BY` can be used in a multi-table update: ```sql 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 | +------------+------+ ``` ## See Also * [Why is ORDER BY in a FROM subquery ignored?](/docs/general-resources/community/community/faq/developer-questions/why-is-order-by-in-a-from-subquery-ignored.md) * [SELECT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md) * [UPDATE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md) * [DELETE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete.md) * [Improvements to ORDER BY Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/improvements-to-order-by.md) * [Joins and Subqueries](/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations.md) * [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md) * [GROUP BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/group-by.md) * [Common Table Expressions](/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions.md) * [SELECT WITH ROLLUP](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-with-rollup.md) * [SELECT INTO OUTFILE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-outfile.md) * [SELECT INTO DUMPFILE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-dumpfile.md) * [FOR UPDATE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/for-update.md) * [LOCK IN SHARE MODE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/lock-in-share-mode.md) * [Optimizer Hints](/docs/server/ha-and-performance/optimization-and-tuning/optimizer-hints.md) _{*This page is licensed: CC BY-SA / Gnu FDL*} {% @marketo/form formId="4316" %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.