Show TOC

Specifying a Sort OrderLocate this document in the navigation structure

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 SELECTstatement 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 s 1 [ASCENDING|DESCENDING]            s 2 [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 .....

Tip

REPORT demo_select_order_by.

DATA: BEGIN OF wa,        carrid TYPE sflight-carrid,        connid TYPE sflight-connid,        min    TYPE i,      END OF wa.

SELECT     carrid connid MAX( 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 looks something like this:

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 in ascending order according to CARRID and in descending order according to this minimum value. Here, the alternative name min is used for the aggregate expression.