ORDER BY specifies the order in which the rows are to be output.
In the first example, all rows are alphabetically ordered according to 'name'.
SELECT name, firstname, city, account
FROM customer
ORDER BY name
In the next example, the rows are arranged in numerically descending (DESC) order. If no order is specified for a sort column, ascending order is always the implicit sorting default. Ascending can also be explicitly specified with ASC.
SELECT name, firstname, city, account
FROM customer
ORDER BY account DESC
A sort column need not be an output column as well.
The position number may be specified in the output list instead of the sort column name:
SELECT name, firstname, city, account
FROM customer
ORDER BY 4 DESC
The order for ASCII code is:
1. Blanks
2. Special characters (%,&,+,-,*,/,...)
3. Numbers
4. Special characters (:,;,<,=,>,...)
5. Capital letters
6. Small letters
7. Null value
The order for EBCDIC code is:
1. Blanks
2. Special characters (+,&,*,;,-,/,%,:,...)
3. Small letters
4. Capital letters
5. Numbers
6. Null value
The type of coding is established while defining the table (see Sections Creating a Table and Data Types in a Table).
To obtain a useful handling and sorting of umlauts and special letters occurring in other languages, ADABAS uses so-called 'MAPCHAR SETs'. A DEFAULTMAP for the conversion of country-specific letters is created during the installation of the database.
The database administrator can create MAPCHAR SETs of his own. When doing so, he defines the way in which special letters have to be mapped to other letters, thus influencing the sort sequence.
For example, if 'ü' (in ASCII represention X'FC') is to be sorted as 'ue', the following assignment must be made:
~ FC ... ue
If 'ü' is to be sorted as 'u', then the line looks like this:
~ FC ... u
To obtain a particular sort sequence for a result table, the function MAPCHAR must be used. If the sort sequence is to deviate from the DEFAULTMAP, a MAPCHAR SET defined by the administrator must be specified as argument of the function.
The statement
SELECT name, firstname, city, account, MAPCHAR(name) lname
FROM customer
ORDER BY lname
produces the desired sort sequence.