Specifying a Sort Order 

The ORDER BY clause sorts the lines in the selection according to the contents of their columns.

If you do not use the ORDER BY clause, the sequence of the lines in the selection is indeterminate, and can vary each time the SELECT statement is executed. You can sort the selection by any column (not necessarily those of the primary key), and specify the columns either statically or dynamically.

Sorting by the Primary Key

To sort the selection set in ascending order by the primary key, use the following:

SELECT <lines> *
...
... ORDER BY PRIMARY KEY.

This sorting method is only possible if you use an asterisk (*) in the SELECT clause to select all columns. Furthermore, it only works if you specify a single database table in the FROM clause. You cannot use views or joins, since neither has a defined primary key.

Sorting by any Columns

To sort the lines in the selection set by any columns, use the following:

SELECT ...
...
ORDER BY <s1> [ASCENDING|DESCENDING]
<s2> [ASCENDING|DESCENDING] ...

The lines are sorted by the columns <s 1 >, <s 2 >, ... You determine the direction of the sort using one of the additions ASCENDING or DESCENDING. The default is ascending order. The sort order depends on the sequence in which you list the columns .

You can use either the names of the columns as specified in the SELECT clause or their alias names. You may only use columns that occur in the SELECT clause. By using alias names for aggregate expressions, you can use them as sort fields.

Specifying the Columns Dynamically

To specify the columns in the ORDER BY clause dynamically, use:

SELECT ...
...
ORDER BY (<itab>).

where <itab> is an internal table with line type C and maximum length 72 characters containing the column names <s 1 > <s 2 > .....

Example

DATA: BEGIN OF WA,
CARRID TYPE SFLIGHT-CARRID,
CONNID TYPE SFLIGHT-CONNID,
MIN TYPE I,
END OF WA.

SELECT CARRID CONNID MIN( SEATSOCC ) AS MIN
INTO CORRESPONDING FIELDS OF WA
FROM SFLIGHT
GROUP BY CARRID CONNID
ORDER BY CARRID MIN DESCENDING.

WRITE: / WA-CARRID, WA-CONNID, WA-MIN.

ENDSELECT.

The output is as follows:

The lines of the database table SFLIGHT are grouped according to the columns CARRID and CONNID, and the system finds the minimum value of the column SEATSOCC for each group. The selection is sorted by CARRID in ascending order and by the minimum value of SEATSOCC in descending order, using the alias name MIN for the aggregate expression.