ABAP Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Read Accesses → SELECT
SELECT - ORDER BY
Other versions: 7.31 | 7.40 | 7.54
Syntax
... ORDER BY { {PRIMARY KEY}
| { {col1|a1} [ASCENDING|DESCENDING],
{col2|a2} [ASCENDING|DESCENDING], ...}
| (column_syntax) } ...
Alternatives
1. ... ORDER BY PRIMARY KEY
2. ... ORDER BY {col1|a1} [ASCENDING|DESCENDING],
{col2|a2} [ASCENDING|DESCENDING], ...
3. ... ORDER BY (column_syntax)
Effect
The addition ORDER BY
sorts a multirow results set by the content of the
specified column. The order of the rows in the results set is undefined with respect to all columns
that are not specified after ORDER BY
, and can be different in repeated executions
of the same SELECT
statement. If the addition ORDER BY
is not specified, the order of all the columns in the results set is undefined.
The following restrictions apply when using the addition ORDER BY
with other additions:
-
If the addition
FOR ALL ENTRIES
is used in theWHERE
condition,,ORDER BY
can only be used with the additionPRIMARY KEY
and all columns of the primary key must be specified afterSELECT
. The additionORDER BY
cannot be used with the additionSINGLE
. -
If aggregate functions
are specified after
SELECT
, all columns that are specified after ORDER BY and that do not have an alternative column name for an aggregation function must also be specified afterSELECT
and afterGROUP BY
. - If an alternative name is used for sorting, this name must be unique and cannot be the same name as a column that does not have any alternative names.
-
If the addition
DISTINCT
is used, only those columns can be specified afterORDER BY
that are also listed afterSELECT
. The exception to this rule is client column when PRIMARY KEY is specified. If not, other columns can also be used, as long as there are no restrictions by other additions such asGROUP BY
.
Notes
-
The data is sorted in the database system, once all other actions are completed, such as the definition
of the hit list using
WHERE
, the calculation of aggregate functions, and grouping usingGROUP BY
. Only the additionUP TO n ROWS
is performed once the hit list is sorted. -
If a column specified after
ORDER BY
contains null values in the results set, the sort order can be platform-dependent, since null values can be sorted either before or after the other values by the database system (in accordance with the SQL standard) -
For performance reasons, a sort should only take place in the database if supported by an
index. This guaranteed only when
ORDER BY PRIMARY KEY
is specified. If a suitable index is not available, the results set must be sorted at runtime. This should be done using SORT on the application server and not usingORDER BY
in the database system. Even if a suitable index does exist,ORDER BY col1 col2 ...
should be used for large amounts of data only if the order of the database fieldscol1 col2 ...
is the same as the order in the index. -
:If a sorted results set is assigned to a sorted internal table, the internal table is sorted again according to the sorting instructions.
Alternative 1
... ORDER BY PRIMARY KEY
Effect
If all columns are specified in the SELECT
list (using *
), and a single database table is specified after FROM
(rather than a
view or a join expression), the
addition PRIMARY KEY
can be used to sort the results set in ascending order in accordance with the content of the
primary key of this database table.
The addition PRIMARY KEY
cannot be specified if the following is specified after FROM
:
- a view that contains the same number of keys fields as view fields
-
a join expression
If this type of view or a join expression is specified after FROM
in a dynamic source_syntax
, the data is sorted by all columns of the results set.
Example
Reads the data from database table SFLIGHT for Lufthansa flight 0400, sorted by flight date.
DATA wa_sflight TYPE sflight.
SELECT *
FROM sflight
WHERE carrid = 'LH' AND
connid = '0400'
ORDER BY PRIMARY KEY
INTO @wa_sflight.
...
ENDSELECT.
Alternative 2
... ORDER BY {col1|a1} [ASCENDING|DESCENDING],
{col2|a2} [ASCENDING|DESCENDING], ...
Effect
For any columns specified in the SELECT
list, a comma-separated list of columns can be specified after ORDER BY
to be used as a sort criterion. Columns can be specified directly using the column names col1
col2 ..., or the alternative column names a1 a2 ...
. The latter is required if columns specified as
aggregate expressions
are to be used as sort criteria. When multiple database tables are accessed and a column name is not unique, the column must be identified using the
column selector ~
.
The additions ASCENDING
and DESCENDING
determine
whether the rows are sorted in ascending or descending order. If neither addition is specified, the
sort is performed in ascending order. The priority of sorting is based on the order in which the components col1 col2...
or a1 a2 ...
are specified.
Pooled and
cluster tables cannot be sorted by all types of column. Columns specified after ORDER BY
cannot be of the
type LCHR, LRAW, STRING, or RAWSTRING.
Notes
-
If single columns are specified in the addition
ORDER BY
, the statementSELECT
uses the SAP buffering only in the following cases:
- The columns specified are a left-justified subset of the primary key in the correct order and no further columns are specified.
- The columns specified represent the whole primary key in the correct order. Additional columns that are specified have no influence on the sorting.
- When a comma-separated list is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.
-
Instead of using commas, blanks can be used to separate columns specified in an
obsolete form. Commas must be specified, however, in the
strict modes of the syntax check from Release 7.40, SP05.
Example
The rows of database table sflight
are grouped by the columns carrid
and connid
, where for each group the minimum of column seatsocc
is determined. The selection is sorted in ascending order by carrid
and in
descending order by the minimum of occupied seats. The alternative name min
is used for the aggregate expression.
TYPES: BEGIN OF wa,
carrid TYPE sflight-carrid,
connid TYPE sflight-connid,
min TYPE i,
END OF wa.
DATA itab TYPE TABLE OF wa WITH EMPTY KEY.
SELECT carrid, connid, MIN( seatsocc ) AS min
FROM sflight
GROUP BY carrid, connid
ORDER BY carrid ASCENDING, min DESCENDING
INTO CORRESPONDING FIELDS OF TABLE @itab.
cl_demo_output=>display_data( itab ).
Alternative 3
... ORDER BY (column_syntax)
Effect
As an alternative to specifying columns statically, a parenthesized data object column_syntax
can be specified, which either contains the syntax of the list of columns or is initial when the statement
is executed. The same applies to column_syntax
as when specifying the SELECT
list dynamically.
The addition PRIMARY KEY
cannot be specified in column_syntax
.
For column_syntax
, the same applies as to specifying columns dynamically
after SELECT
. If the content of column_syntax
is initial, the addition ORDER BY
is ignored.
Security Note
If used wrongly, dynamic programming techniques can present a serious security risk. Any dynamic content
that is passed to a program from the outside must be checked thoroughly or escaped before being used
in dynamic statements. This can be done using the system class CL_ABAP_DYN_PRG or the predefined function escape
. See
SQL Injections Using Dynamic Tokens.
Notes
-
The class CL_ABAP_DYN_PRG contains methods that support dynamically specified columns when created correctly and securely.
- The literals of the dynamically specified Open SQL statements can span multiple rows of a token specified dynamically as an internal table.
- When specified dynamically, Open SQL statements can contain the comment characters
*
and"
as follows:
- In a dynamic token specified as a character-like data object, all content is ignored from the first comment character
"
.
- In a dynamic token specified as an internal table, all rows are ignored that start with the comment
character
*
. In the row, all content is ignored from the first comment character"
.
Example