|
|
|
|
|
The ORDER BY clause sorts the rows retrieved by a SELECT statement. The syntax is as follows:
ORDER BY field_reference [ASC|DESC] [, field_reference... [ASC|DESC]] [NOCASE]
Use an ORDER BY clause to sort the rows retrieved by a SELECT statement based on the values from one or more fields. In SELECT statements, use of the clause is optional.
The value for the ORDER BY clause is a comma-separated list of field names. The fields in this list must also be in the SELECT clause of the query statement. Fields in the ORDER BY list can be from one or multiple tables. If the fields used for an ORDER BY clause come from multiple tables, the tables must all be those that are part of a join, as they cannot be a table included in the statement only through a SELECT subquery.
A field can be specified in an ORDER BY clause using a number representing the relative position of the field in the SELECT of the statement. Field correlation names can also be used in an ORDER BY clause fields list. Calculations can be used directly in an ORDER BY clause.
Use ASC to force the sort to be in ascending order (smallest to largest), or DESC for a descending sort order (largest to smallest). When not specified, ASC is the implied default.
Use NOCASE to force the sort to be case-insensitive. This is also useful for allowing a live result set when an index is available that matches the ORDER BY clause but is marked as case-insensitive. When not specified, case-sensitive is the implied default.
The statement below sorts the result set ascending by the year extracted from the DocumentDate field, then descending by the SalesPersonCode field, and then ascending by the CustomerCode field:
SELECT EXTRACT(YEAR FROM DocumentDate) AS YY, SalesPersonCode, CustomerCode
FROM INHEAD
ORDER BY YY DESC, SalesPersonCode ASC, 3