The ORDER BY clause sorts the query result set in ascending or descending order. If you do not specify a sorting option such as ASC or DESC, the result set in ascending order by default. If you do not specify the ORDER BY clause, the order of records to be queried may vary depending on query.
SELECT ...
ORDER BY {col_name | expr | position} [ASC | DESC],...]
[ FOR <orderby_for_condition> ] ]
<orderby_for_condition> ::=
ORDERBY_NUM() { BETWEEN int AND int } |
{ { = | =< | < | > | >= } int } |
IN ( int, ...)
--selecting rows sorted by ORDER BY clause
SELECT * FROM sales_tbl ORDER BY dept_no DESC, name ASC;
dept_no name sales_month sales_amount
==============================================================
501 'Chang' 5 150
501 'Stephan' 4 100
501 'Sue' 6 150
301 'Max' 4 300
201 'George' 1 450
201 'Laura' 2 500
NULL 'Yoka' 4 NULL
--sorting reversely and limiting result rows by LIMIT clause
SELECT dept_no AS a1, avg(sales_amount) AS a2 FROM sales_tbl
GROUP BY a1 ORDER BY a2 DESC LIMIT 0,3;
a1 a2
==========================
201 475
301 300
501 133
--sorting reversely and limiting result rows by FOR clause
SELECT dept_no AS a1, avg(sales_amount) AS a2 FROM sales_tbl
GROUP BY a1 ORDER BY a2 DESC FOR ORDERBY_NUM() BETWEEN 1 AND 3;
a1 a2
==========================
201 475
301 300
501 133