ORDER BY clause
Variants
1. ... ORDER BY PRIMARY KEY
2. ... ORDER BY f1 ... fn
3. ... ORDER BY (itab)
Effect
Orders the records in a
SELECT command. Without the
ORDER-BY clause, the order in which the selected lines are
supplied is undefined. This means that two similar SELECT
commands may produce lines in a different order.
Variant 1
...ORDER BY PRIMARY KEY
Effect
Sorts the selected lines in ascending order by the
primary key of the database table. This
variant is only permitted for SELECT * ... .
Example
Output the passenger list for the Lufthansa flight 0400
on 28.02.1995:
-
TABLES SBOOK.
SELECT * FROM SBOOK
WHERE
CARRID = 'LH ' AND
CONNID = '0400' AND
FLDATE = '19950228'
ORDER BY PRIMARY KEY.
WRITE: / SBOOK-BOOKID, SBOOK-CUSTOMID, SBOOK-CUSTTYPE,
SBOOK-SMOKER, SBOOK-LUGGWEIGHT, SBOOK-WUNIT,
SBOOK-INVOICE.
ENDSELECT.
Notes
Since views do not have a primary key, specifying
ORDER BY PRIMARY KEY only makes sense with database tables. If,
however, you do specify ORDER BY PRIMARY KEY with a view, all
fields of the view are sorted in ascending order.
Variant 2
ORDER BY f1 ... fn
Effect
Sorts the selected records in ascending order by the
specified database fields f1 ... fn . If a list is also specified
in the SELECT clause , the fields
f1, ..., fn must appear in this list.
By supplementing the statement with DESCENDING , you can sort in
descending order using any of the fields f1, ..., fn .
The default sort sequence is ascending order, but you can make this
explicit by adding the addition ASCENDING .
Examples
Output Lufthansa flights from 27.02.1995 to
05.03.1995, sorted by plane type and number of occupied seats:
-
TABLES: SFLIGHT.
SELECT * FROM SFLIGHT
WHERE CARRID = 'LH' AND
FLDATE BETWEEN '19950227' AND '19950305'
ORDER BY PLANETYPE ASCENDING SEATSOCC DESCENDING.
WRITE: / SFLIGHT-PLANETYPE, SFLIGHT-SEATSOCC, SFLIGHT-CONNID,
SFLIGHT-FLDATE.
ENDSELECT.
Notes
Pooled and cluster tables can only be sorted by their
primary key.
With a SELECT * ... , the client field automatically becomes
the first sort criterion in client-specific tables, unless the addition
CLIENT SPECIFIED is specified in the
FROM clause .
Specifying FOR ALL ENTRIES IN itab WHERE ... in the
WHERE clause excludes ORDER BY f1
... fn .
Notes
Performance
Notes
In contrast to ... ORDER BY PRIMARY KEY , ORDER
BY f1 ... fn is not automatically supported by a (sorted)
index . Without an index, you must sort the result
set at runtime. Because of the SAP architecture, this should not be
performed on the database server, but on the applications server. If it
does not make sense to create an index, you should not sort the result
set with ... ORDER BY f1 ... fn on the database server, but with
SORT on the applications server.
With larger datasets, you should only use the variant ORDER BY f1
... fn if the order of the database fields f1 ... fn
is exactly the same as the order of the indexes.
Variant 3
... ORDER BY (itab)
Effect
Works like ORDER BY f1 ... fn if the
internal table itab contains the list f1 ... fn as
ABAP/4 source code. The internal table itab must only
have one field. This must be a type C field and must not be more
than 72 characters long. itab must be specified in parentheses.
There must be no blanks between the parentheses and the table name.
Note
The same restrictions apply to this variant as to
ORDER BY f1 ... fn .
Example
Output all Lufthansa points of departure with the
number of destinations:
-
TABLES: SPFLI
DATA: BEGIN OF WA.
INCLUDE STRUCTURE SPFLI.
DATA: COUNT TYPE I:
DATA: END OF WA.
DATA: GTAB(72) OCCURS 5 WITH HEADER LINE,
FTAB(72) OCCURS 5 WITH HEADER LINE,
OTAB(72) OCCURS 5 WITH HEADER LINE,
COUNT TYPE I.
REFRESH: GTAB, FTAB, OTAB.
FTAB = 'CITYFROM COUNT( * ) AS COUNT'. APPEND FTAB.
GTAB = 'CITYFROM'. APPEND GTAB.
OTAB = 'CITYFROM'. APPEND OTAB.
SELECT DISTINCT (FTAB)
INTO CORRESPONDING FIELDS OF WA
FROM SPFLI
WHERE
CARRID = 'LH'
GROUP BY (GTAB)
ORDER BY (OTAB).
-
WRITE: / WA-CITYFROM, WA-COUNT.
ENDSELECT.
Index
© SAP AG 1996