ORDER BY Clause

Specifies the order in which rows are displayed in a result table.

See:	query-expression
	Retrieving Values with the SOUNDS-LIKE Operator

Syntax

Required Arguments

Details

Syntax

ORDER BY order-by-item <ASC|DESC><, … order-by-item <ASC|DESC>>;

Required Arguments

order-by-item

is one of the following:

integer

equates to a column's position.

column-name

is the name of a column or a column alias. See column-name.

sql-expression

See sql-expression.

ASC

orders the data in ascending order. This is the default order. If neither ASC nor DESC is specified, the data is ordered in ascending order.

DESC

orders the data in descending order.

Details

The ORDER BY clause sorts the results of a query expression according to the order specified in that query. When this clause is used, the default ordering sequence is ascending, from the lowest value to the highest. You can use the SORTSEQ= option to change the collating sequence for your output. See PROC SQL Statement.
The order of the output rows that are returned is guaranteed only for columns that are specified in the ORDER BY clause.

Note: The ORDER BY clause does not guarantee that the order of the rows generated is deterministic. The ANSI standard for SQL allows the SQL implementation to specify whether the ORDER BY clause is stable or unstable. If the joint combination of values that is referenced in an ORDER BY clause for a query are unique in all of the rows that are being ordered, then the order of rows that is generated by ORDER BY is always deterministic. However, if the ORDER BY clause does not reference a joint combination of unique values, then the order of rows is not deterministic if ORDER BY is unstable.
If an ORDER BY clause is omitted, then a particular order to the output rows, such as the order in which the rows are encountered in the queried table, cannot be guaranteed—even if an index is present. Without an ORDER BY clause, the order of the output rows is determined by the internal processing of PROC SQL, the default collating sequence of SAS, and your operating environment.
If more than one order-by-item is specified (separated by commas), then the first one determines the major sort order.
Integers can be substituted for column names (that is, SELECT object-items) in the ORDER BY clause. For example, if the order-by-item is 2 (an integer), then the results are ordered by the values of the second column. If a query expression includes a set operator (for example, UNION), then use integers to specify the order. Doing so avoids ambiguous references to columns in the table expressions. Note that if you use a floating-point value (for example, 2.3) instead of an integer, then PROC SQL ignores the decimal portion.
In the ORDER BY clause, you can specify any column of a table or view that is specified in the FROM clause of a query expression, regardless of whether that column has been included in the query's SELECT clause. For example, this query produces a report ordered by the descending values of the population change for each country from 1990 to 1995:
```
proc sql;
   select country
      from census
      order by pop95-pop90 desc;
```
```
NOTE: The query as specified involves
      ordering by an item that
      doesn't appear in its SELECT clause.
```
You can order the output by the values that are returned by an expression. For example, if X is a numeric variable, then the output of the following is ordered by the integer portion of values of X:
```
select x, y
from table1
order by int(x);
```
Similarly, if Y is a character variable, then the output of the following is ordered by the second character of values of Y:
```
select x, y
from table1
order by substring(y from 2 for 1);
```
Note that an expression that contains only numeric literals (and functions of numeric literals) or only character literals (and functions of character literals) is ignored.