ORDER BY CLAUSE

Description

The ORDER BY clause specifies the sorting of rows retrieved by the SELECT statement. c-treeACE SQL does not guarantee the sort order of rows unless the SELECT statement includes an ORDER BY clause.

Syntax

ORDER BY { expr | posn } [ ASC | DESC ]

      [ , { expr | posn } [ASC | DESC] , … ]

Notes

• Ascending order is the default ordering. The descending order will be used only if the keyword DESC is specified for that column.
• Each expr is an expression of one or more columns of the tables specified in the FROM clause of the SELECT statement. Each posn is a number identifying the column position of the columns being selected by the SELECT statement.
• The selected rows are ordered on the basis of the first expr or posn and if the values are the same then the second expr or posn is used in the ordering.
• The ORDER BY clause if specified should follow all other clauses of the SELECT statement.
• An ORDER BY clause can appear in a subquery, however, subqueries with a combination of TOP, GROUP BY and outer references remain unsupported.
• An ORDER BY clause can appear in a FOR UPDATE query.

  Note: This is a non-standard SQL feature as specified by SQL92. Also, full cursor update is not supported by c-treeACE SQL.

• A query expression followed by an optional ORDER BY clause can be specified. In such a case, if the query expression contains set operators, then the ORDER BY clause can specify only the positions. For example:

   -- Get a merged list of customers and suppliers

   -- sorted by their name.

        (SELECT name, street, state, zip

        FROM customer

        UNION

        SELECT name, street, state, zip

        FROM supplier)

        ORDER BY 1 ;

Example

SELECT name, street, city, state, zip

     FROM customer

     ORDER BY name ;