Order By Clause
The order by clause lets you specify how the returned rows are ordered. Its syntax is:
order by orderspecification{, orderspecification}
where orderspecification is:
name |number [sortorder]
You can use either the names or numbers to specify the order, but not both. Additionally, if you use a dynamic name to specify either name or sortorder, you cannot use the repeated option in the select statement.
The name must be taken from a simple_variable, resultname, or dbexpression (if it specifies a database column and simple_variable and resultname are omitted) in the select's target list. If the name in the select target list is expressed with dot notation, use the last component of that notation as the name in the order by clause. For example:
select empname as name,
empage as e.age,
empsalary as emptab[3].salary
from emptable
order by age, salary, name
The number is the ordinal position of the name in the select target list. For example, consider the following select statement:
select empname as e.name
department as e.dept
from emptable
order by 2, 1
begin...
In this statement, e.name is in ordinal position one in the target list and e.dept is in ordinal position two. In the order by clause, we are directing the DBMS to order the returned rows by the variable name in position two, that is, e.dept (the department) and then by the variable name in position one, e.name.
If you include more than one name or number, the DBMS orders the rows on the basis of the first item, the second item, and so on. For example, assume that your application has the following select statement:
select ename as employee_name, dept as dept,
salary as salary, eage as age
from employee
order by dept, employee_name, salary asc;
If the returned rows are displayed in a table field, the user sees them sorted in this manner:
The rows are sorted first by department, then within each department by name, and within each name (if two are the same) by salary.
The sortorder determines how each item is sorted within itself. You can specify an ascending (asc) or descending (desc) sort order for each item. If you do not specify sortorder, the default is ascending.
If you are sorting by names that are the same, you must use numbers. For example, numbers must be used in the following order by clause:
select empname as e.name,
mgrname as m.name
from emptable
order by 1, 2