ORDER BY Clause

Description

The ORDER BY clause sorts the query result set in ascending or descending order. The ORDER BY clause must be placed at the end of the query statement. If the ORDER BY clause is not specified, the order of instances to be queried may vary depending on query.

Syntax

ORDER BY sort_spec [ {, sort_spec}  ]
sort_spec :
integer_literal [ ASC | DESC ]
expression [ ASC | DESC ]
alias [ ASC | DESC ]

• sort_spec : The sort_spec can be one or more column names, expressions, aliases or integer values.
  • integer_literal [ ASC| DESC] : An integer value integer_literal is used as a reference to the position of the column or expression in the SELECT clause.
  • expression [ ASC| DESC]
  • alias [ ASC| DESC]

Each field in the ORDER BY clause must be separated by a comma (,). If the keyword DESC is not specified after expression or integer_literal in the ORDER BY clause, the query result is returned in ascending order.

Example

• Example 1
• The following is an example of retrieving the years and host countries of the Olympic Games and sorting the results by host country in ascending order. The results are sorted by the column after the ORDER BY clause in ascending order. To sort in descending order, place DESC after the column.

SELECT host_year, host_nation FROM olympic ORDER BY host_nation; 
=== <Result of SELECT Command in Line 2> ===
    host_year  host_nation
===================================
         2000  'Australia'
         1956  'Australia'
         1920  'Belgium'
         1976  'Canada'
         1952  'Finland'
...
 25 rows selected.

• Example 2
• The following example of retrieving the countries which won between 10 and 15 gold medals. If the numbers of gold medals are the same, the results are sorted by nation code in ascending order. The query statement below shows how integer values are used in the ORDER BY clause. The integer value corresponding to the position of the column or expression used in the SELECT statement is used to determine the sorting order of the query results. Multiple columns are displayed below to indicate the sorting order.

SELECT host_year, nation_code, gold FROM participant WHERE gold BETWEEN 10 AND 15
ORDER BY 3 DESC, 2;
 === <Result of SELECT Command in Line 1> ===
    host_year  nation_code                  gold
================================================
         1996  'FRA'                          15
         1992  'CUB'                          14
         1992  'ESP'                          13
         2000  'FRA'                          13
         2000  'GER'                          13
         2004  'GER'                          13
...    
20 rows selected.