SELECT - ORDER BY
Short Reference
ABAP Syntax ... ORDER BY { {PRIMARY KEY}
| { {col1|a1} [ASCENDING|DESCENDING]
{col2|a2} [ASCENDING|DESCENDING] ...}
| (column_syntax) } ... .
ABAP_ALTERNATIVES:
1 ... ORDER BY PRIMARY KEY
2 ... ORDER BY {col1|a1} [ASCENDING|DESCENDING]
{col2|a2} [ASCENDING|DESCENDING] ...
3 ... ORDER BY (column_syntax)
What does it do? The addition ORDER BY sorts a multirow results set
by the content of the specified column. The order of the rows in the
result set refers to all columns that are not listed after
ORDER BY
, is undefined, and can be different in repeated executions of the
same SELECT statement.
The following restrictions apply when using the ORDER BY
addition with other additions:
If the addition FOR ALL ENTRIES
is used in the WHERE condition, , ORDER BY
can only be used with the addition PRIMARY KEY and all
columns of the primary key must be listed after SELECT . The
addition ORDER BY cannot be used with the addition
SINGLE .
If aggregate functions are
specified after SELECT , all columns that are listed after
ORDER BY and that do not have an alternative column name for an
aggregation function must also be specified after SELECT and
after GROUP BY .
If you use an alternative name 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 after ORDER BY that
are also listed after SELECT . If not, other columns can also be
used, as long as there are no restrictions by other additions such as
GROUP BY .
Latest notes: The data is sorted in the database system, once all other
actions are completed, such as the definition of the hitlist using
WHERE , the calculation of aggregate functions, and grouping using
GROUP BY . Only the addition UP TO n ROWS is performed once
the hitlist 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 using ORDER 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 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.
ABAP_ALTERNATIVE_1 ... ORDER BY PRIMARY KEY
What does it do? If all columns are specified (by
* after SELECT ), 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
resulting set in ascending order according to the content of the
primary key of this database table.
The addition PRIMARY KEY cannot be specified if a view or a
join expression is statically specified after FROM . If a view or
a join expression is specified after FROM in a dynamic
dbtab_syntax , the data is sorted by all columns of the resulting set.
Example ABAP Coding Reads the data from database table
SFLIGHT for Lufthansa flight 0400,
sorted by flight date.
DATA wa_sflight TYPE sflight.
SELECT * FROM sflight
INTO wa_sflight
WHERE carrid = 'LH' AND
connid = '0400'
ORDER BY PRIMARY KEY.
ENDSELECT.
ABAP_ALTERNATIVE_2 ... ORDER BY {col1|a1} [ASCENDING|DESCENDING]
{col2|a2} [ASCENDING|DESCENDING] ...
What does it do? For any columns specified
after SELECT , a 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 LCHAR , LRAW , STRING , or RAWSTRING .
Latest notes: If single columns are specified in the addition ORDER
BY , the statement SELECT 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.
In other cases, SAP buffering is ignored.
Example ABAP Coding 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
INTO CORRESPONDING FIELDS OF TABLE itab
GROUP BY carrid connid
ORDER BY carrid min DESCENDING.
cl_demo_output=>display_data( itab ).
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, which
either contains the syntax of the list of columns or is initial when the
statement is executed. 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.
See SQL Injections Using Dynamic
Tokens .
Latest notes: The class CL_ABAP_DYN_PRG
contains methods that support the creation of correct and secure
dynamic column specifications.
Example ABAP Coding Selecting the database table sflight in a method,
whereby the sort criterion is passed as an input parameter. In this
case, the user must enter the criterion using the correct syntax on the
selection screen . In a proper
application, input helps are usually prepared using a selection list.
TYPES sflight_table_type TYPE TABLE OF sflight.
CLASS handle_sflight DEFINITION.
PUBLIC SECTION.
CLASS-METHODS select_sort_sflight
IMPORTING sort_crit TYPE string
EXPORTING sflight_tab TYPE sflight_table_type
RAISING cx_sy_dynamic_osql_error.
ENDCLASS.
CLASS handle_sflight IMPLEMENTATION.
METHOD select_sort_sflight.
SELECT *
FROM sflight
INTO TABLE sflight_tab
ORDER BY (sort_crit).
ENDMETHOD.
ENDCLASS.
PARAMETERS p_sort TYPE c LENGTH 40.
DATA: s_sort TYPE string,
result_tab TYPE sflight_table_type,
cols TYPE TABLE OF string,
col TYPE string.
AT SELECTION-SCREEN.
CONDENSE p_sort.
REPLACE ALL OCCURRENCES OF REGEX `(AS)|(DES)CENDING`
IN p_sort WITH ``.
SPLIT p_sort AT ` ` INTO TABLE cols.
TRY.
LOOP AT cols INTO col.
cl_abap_dyn_prg=>check_column_name( col ).
ENDLOOP.
CATCH cx_abap_invalid_name.
MESSAGE 'Not allowed' TYPE 'E'.
ENDTRY.
START-OF-SELECTION.
TRY.
s_sort = p_sort.
handle_sflight=>select_sort_sflight(
EXPORTING sort_crit = s_sort
IMPORTING sflight_tab = result_tab ).
CATCH cx_sy_dynamic_osql_error.
MESSAGE `Wrong sort criterium!` TYPE 'I'.
ENDTRY.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved