SAP ORDERBY CLAUSE ABAP Statements

Get Example source ABAP code based on a different SAP table

• ORDER BY SELECT

SELECT, ORDER BY
Short Reference

ABAP_SYNTAX
... ORDER BY
${ ${PRIMARY KEY$}
$| ${ ${col1$|a1$| sql_exp1$} $[ASCENDING$|DESCENDING$] $[${NULLS FIRST$}$|${NULLS LAST$}$],
${col2$|a2$| sql_exp2$} $[ASCENDING$|DESCENDING$] $[${NULLS FIRST$}$|${NULLS LAST$}$], ...$}
$| (column_syntax) $} ...

ABAP_ALTERNATIVES:
1 ... ORDER BY PRIMARY KEY
2 ... ORDER BY ${col1$|a1$|sql_exp1$} $[ASCENDING$|DESCENDING$] $[${NULLS FIRST$}$|${NULLS LAST$}$],
${col2$|a2$|sql_exp2$} $[ASCENDING$|DESCENDING$] $[${NULLS FIRST$}$|${NULLS LAST$}$], ...
3 ... ORDER BY (column_syntax)

What does it do?
The addition ORDER BY sorts a multirow result set of a query by the content of the specified column. The order of the rows in the result set is undefined with respect to all columns that are not listed 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 the rows in the result set is undefined with respect to all its columns.
The following restrictions apply when using the addition ORDER BY with other additions:

The addition ORDER BY cannot be used with the addition SINGLE.

All columns specified after ORDER BY must also be specified after the addition GROUP BY at the same time.

If aggregate functions are specified after SELECT, all columns that are specified after ORDER BY and that do not have an alias name for an aggregate function must also be specified after SELECT and after GROUP BY.

If an alias name defined using AS is used for sorting, this name must be unique and cannot be the same name as a column to which no alias name is assigned.

If the addition DISTINCT is used, only those columns can be specified after ORDER BY that are also listed after SELECT. The exception to this rule is the 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 as GROUP BY.

If the addition FOR ALL ENTRIES is used in front of the WHERE condition, ORDER BY can only be used with the addition PRIMARY KEY and all columns of the primary key, except the client column of client-dependent tables, must be specified in the SELECT list.

Latest notes:

The data is sorted in the database system, once all other actions are completed, such as determining the hit list using WHERE, calculating aggregate functions, and grouping using GROUP BY . Only the additions UP TO and OFFSET are executed on the sorted hits.

Sorts in the database system are performed in accordance with the rules for size comparisons and the restrictions with regard to platform dependencies apply. More specifically, sorts after character-like values can be platform-dependent under certain circumstances and produce different results than ABAP sorts.

For performance reasons, a sort should only take place in the database if supported by an index. This is only guaranteed when ORDER BY PRIMARY KEY is specified. If a suitable index is not available, the result set must be sorted at runtime. This should be done using SORT on AS ABAP and not using ORDER BY in the database system. Even if there is a suitable index, ORDER BY col1, col2 ... should be used for large amounts of data only if the order of the database fields col1, col2 ... is the same as the order in the index.

If a sorted resulting set is assigned to a sorted internal table, the internal table is sorted again according to the sorting instructions.
NON_V5_HINTS
ABAP_HINT_END
• PRIMARY KEY SELECT ORDER BY

ABAP Alternative 1 ... ORDER BY PRIMARY KEY

What does it do?
The result set is sorted in ascending order by the content of the primary key of a single data source. The following restrictions apply:

The addition PRIMARY KEY cannot be specified if a join expression or a path expression is used in the SELECT statement to select the data of multiple data sources.

The addition PRIMARY KEY cannot be specified in a subquery.

The addition PRIMARY KEY cannot be specified for a result set joined with UNION, INTERSECT , or EXCEPT.

The addition PRIMARY KEY cannot be used when accessing a common table expression defined using WITH.
BEGIN_SECTION VERSION 5 OUT

The addition PRIMARY KEY cannot be specified when views that contain exactly the same number of key fields as view fields are accessed. If a DDIC database view like this is specified after FROM in the dynamically specified source_syntax, an exception is raised only in the strict modes of the syntax check from ABAP_RELEASE ABAP_740_SP05 . In all other cases, the result set is sorted by all columns.
END_SECTION VERSION 5 OUT
BEGIN_SECTION SAP_INTERNAL_HINT
For DDIC projection views, dynamic always works as static.
END_SECTION SAP_INTERNAL_HINT

If a CDS entity is sorted by the PRIMARY KEY, its key elements must be defined at the start of the structure without any gaps.

Latest notes:
If ORDER BY PRIMARY KEY is used with the addition FOR ALL ENTRIES in front of the WHERE condition, all fields of the primary key , except for the client column in client-dependent table, must be in the SELECT list.
NON_V5_HINTS
ABAP_HINT_END

ABAP_EXAMPLE_VX5
Reading of the data from the DDIC database table SFLIGHT for Lufthansa flight 0400, sorted by the third key field, the flight date.
ABEXA 00480
ABAP_EXAMPLE_END
• ASCENDING SELECT ORDER BY
• DESCENDING SELECT ORDER BY
• , SELECT ORDER BY
• NULLS FIRST SELECT ORDER BY
• NULLS LAST SELECT ORDER BY

ABAP Alternative 2 ... ORDER BY ${col1$|a1$|sql_exp1$} $[ASCENDING$|DESCENDING$] $[${NULLS FIRST$}$|${NULLS LAST$}$],
${col2$|a2$|sql_exp2$} $[ASCENDING$|DESCENDING$] $[${NULLS FIRST$}$|${NULLS LAST$}$], ...

ABAP Addition
1 ... ASCENDING$|DESCENDING
2 ... ${NULLS FIRST$}$|${NULLS LAST$}

What does it do?
With this variant, columns or expressions can be specified in a comma-separated list as a sort criterion.

With col1, col2, ..., any columns of the SELECT list can be specified directly by their column names.

With a1, a2, ..., columns can be specified using alias names defined with AS in the SELECT list. This is necessary if sorting is to be done by columns of the result set that are defined in the SELECT list using non-elementary SQL expressions.

sql_exp1, sql_exp2, ..., can be used to specify SQL expressions. Alias names can be used as operands of the SQL expressions.

Latest notes:

Alias names specified in the SELECT list must be handled with care. Especially, non-alphanumeric characters should be prevented. In the following example, the sorting is carried out based on three columns: 1, +, 1: SELECT col1 as 1 col2 as + FROM ... ORDER BY 1 + 1 ...

Columns that are specified for an SQL expression used in the ORDER BY clause need to be specified in the GROUP BY clause. If the same SQL expression is specified in the GROUP BY clause, the columns do not need to be specified there again.

Alias names that are used in an SQL expression in the ORDER BY clause do not need to be specified in the GROUP BY clause.

If only aggregate functions are specified after SELECT and in the ORDER BY clause, a SELECT statement that opens a loop must not be closed using the statements ENDSELECT or ENDWITH when reading into a non-table-like target area.

Currently, SQL expressions are not supported in the ORDER BY clause of SELECT statements using UNION , INTERSECT, and EXCEPT .

If single columns are specified in the addition ORDER BY, the statement SELECT uses table buffering only in the following cases:

The columns specified are a left-aligned subset of the primary key in the correct order and no further columns are specified.

The columns specified represent the entire primary key in the correct sequence. Additional columns that are specified have no effect on the sorting.

The addition DESCENDING is not specified. In other cases, table buffering is ignored.
NON_V5_HINTS

When a comma-separated list is used, the syntax check is performed in a ABAP_STRICT_740_SP05 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 used, however, in the strict modes of the syntax check from ABAP_RELEASE ABAP_740_SP05 .

SQL expressions rely on the SQL parser introduced with ABAP_RELEASE 7.40, SP02. They can only be used in queries where the corresponding syntax is recognizable, e. g. for @ in front of host variables, in comma-separated lists, or queries in CTEs.

If specified, the columns col1, col2, ... can contain a path expression for CDS associations or CTE associations.
ABAP_HINT_END

ABAP_EXAMPLE_VX5
Reading of data from the DDIC database table SFLIGHT by sorting the result table by the overall number of rows using an aggregate expression.
ABEXA 01651
ABAP_EXAMPLE_END

ABAP Addition

What does it do?
The additions ASCENDING and DESCENDING determine whether the column or expression in question is sorted in ascending or descending order. If neither addition is specified, the column or expression is sorted in ascending order. The priority of sorting is based on the order in which the components col1, col2... or a1, a2 ... are specified.

ABAP_EXAMPLE_VX5
The rows of DDIC 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.
ABEXA 00481
ABAP_EXAMPLE_END

ABAP Addition

What does it do?
The additions NULLS FIRST and NULLS LAST determine whether null values are placed in front of or after non-null values. If neither addition is specified, potential null values are placed at the beginning of the result set. If only DESCENDING is specified and no nulls occur, null values are placed at the end of the result set.
The following table shows the default behavior of the additions ASCENDING, DESCENDING, NULLS FIRST, and NULLS LAST. AdditionDefault Behavior of Result Set
No additionASCENDING + NULLS FIRST
NULLS LASTASCENDING + NULLS LAST
DESCENDINGDESCENDING + NULLS LAST
Columns specified after ORDER BY cannot be of the type LCHR, LRAW, STRING , RAWSTRING, or GEOM_EWKB.

Latest notes:
NON_V5_HINTS

The additions NULLS FIRST and NULLS LAST are not supported by all databases. In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports the use of these additions. This requires the constant ORDER_BY_NULLS_FIRST_LAST of this class to be passed to the method in an internal table.

NULLS FIRST and NULLS LAST enforce ABAP_STRICT_778 strict mode from ABAP_RELEASE ABAP_778 / .
ABAP_HINT_END

ABAP_EXAMPLE_VX5
To generate null values, a left outer join is performed on table DEMO_SFLIGHT_AGG and the result set is sorted by its column CARRID with nulls at the end of the table. The class CL_DEMO_SELECT_ORDER_NULLS executes the statement and displays the result.
ABEXA 01428
IMAGE ABDOC_SELECT_ORDER_NULL.png 555 152
ABAP_EXAMPLE_END

ABAP Alternative 3 ... ORDER BY (column_syntax)

What does it do?
As an alternative to specifying columns statically, a parenthesized data object column_syntax can be specified that contains the syntax of PRIMARY KEY or 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. If the content of column_syntax is initial, the addition ORDER BY is ignored. Invalid syntax raises a catchable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.