ORDER BY Clause

Description

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.

Syntax

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, ...)

Example

--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