Try OpenEdge Now
skip to main content
SQL Reference
SQL Reference : OpenEdge SQL Statements : SELECT : ORDER BY clause
 
ORDER BY clause
Allows ordering of the rows selected by the SELECT statement. Unless an ORDER BY clause is specified, the rows of the result set might be returned in an unpredictable order as determined by the access paths chosen and other decisions made by the query optimizer. The decisions made will be affected by the statistics generated from table and index data examined by the UPDATE STATISTICS command.

Syntax

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

Parameters

expr
Expression of one or more columns of the tables specified in the FROM clause of the SELECT statement.
posn
Integer column position of the columns selected by the SELECT statement.
ASC | DESC
Indicates whether to order by ascending order (ASC) or descending order. The default is ASC.

Notes

*The ORDER BY clause, if specified, should follow all other clauses of the SELECT statement.
*The selected rows are ordered on the basis of the first expr or posn. If the values are the same, then the second expr or posn is used in the ordering.
*A query expression can be followed by an optional ORDER BY clause. If the query expression contains set operators (such as, UNION), then the ORDER BY clause can specify column names, aliases, and positions in the following ways:
*An alias from the first SELECT statement of the set operator query expression can be used as the ORDER BY key. In this case, the alias indicates the ordering based on the corresponding column from all the query expressions (SELECT statements) in the set operator.
*A column name from the first SELECT statement of the set operator query expression can be explicitly used as an ORDER BY key. In this case, the column name indicates the ordering based on the corresponding column from all the query expressions (SELECT statements) in the set operator.
*An ordinal position number can be specified as an ORDER BY key.
*A query expression is considered invalid if the ORDER BY clause:
*Has an expression
*Refers to a column name or alias that is not from the first SELECT statement
*Has a position number that is greater than the number of columns projected

Examples

ORDER BY clause
The following example demonstrates the ORDER BY clause in the SELECT statement:
-- Produce a list of customers sorted by name.
SELECT Name, Address, City, State, PostalCode
FROM Customer
ORDER BY Name ;
ORDER BY clause in query expressions containing set operators
The following examples demonstrate the ORDER BY clause in query expressions containing set operators:
-- Produce a merged list of customers and suppliers with the column position specifying the sort key.
SELECT Name, Address, State, PostalCode
FROM Customer
UNION
SELECT Name, Address, State, PostalCode
FROM Supplier
ORDER BY 1;
-- Produce a merged list of customers and suppliers sorted by name.
SELECT Name, Address, State, PostalCode
FROM Customer
UNION
SELECT Name, Address, State, PostalCode
FROM Supplier
ORDER BY Name;
-- Produce a merged list of customers and suppliers sorted by name.
SELECT cust_name as Name, cust_address, cust_state, cust_postalcode
FROM Customer
UNION
SELECT sup_name, sup_address, sup_state, sup_postalcode
FROM Supplier
ORDER BY Name;