topic Re: Sorting Lists in Application Development and Automation Discussions

Sorting Lists

Former Member — Mon, 12 Jun 2006 08:59:21 GMT

Gday,

I am selecting from a table into the WA, then want to display the selected rows but sorted in a particular way. Is it possible to order rows as I select them, or do I have to select them into an itab so i can order them?

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:01:05 GMT

Hi,

Sure you can using the ORDER BY clause in the select. However, for performance reasons it would be better to select all records in an internal table and then perform the sort.

Regards,

John.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:02:38 GMT

chk this

DATA: wa_sflight TYPE sflight. 

SELECT * FROM sflight INTO wa_sflight 
         WHERE carrid = 'LH' AND 
               fldate BETWEEN '20010227' AND '20010305' 
         ORDER BY planetype ASCENDING seatsocc DESCENDING. 
  WRITE: / wa_sflight-planetype, wa_sflight-seatsocc, 
           wa_sflight-connid, wa_sflight-fldate. 
ENDSELECT.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:03:31 GMT

You can use the 'ORDER BY' clause in the SELECT statement. F1 help on SELECT should help you with the syntax.

SELECT * FROM SBOOK INTO WA_SBOOK
         WHERE
           CARRID  = 'LH '      AND
           CONNID  = '0400'     AND
           FLDATE  = '19950228'
         ORDER BY PRIMARY KEY.

Sudha

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:03:44 GMT

using sort command u can sort the WA in particular

field

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:04:20 GMT

chk this help of SELECT

ORDER BY Clause

Variants:

1. ... ORDER BY PRIMARY KEY

2. ... ORDER BY f1 ... fn

3. ... ORDER BY (source_text)

Effect

Orders the records in a SELECT statement. Without the ORDER-BY clause, the order in which the selected lines are supplied is undefined. This means that two similar SELECT statements 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.2001:

DATA: wa_sbook TYPE sbook.

SELECT * FROM sbook INTO wa_sbook

WHERE

carrid = 'LH ' AND

connid = '0400' AND

fldate = '20010228'

ORDER BY PRIMARY KEY.

WRITE: / wa_sbook-bookid, wa_sbook-customid,

wa_sbook-custtype, wa_sbook-smoker,

wa_sbook-luggweight, wa_sbook-wunit,

wa_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 column references f1 ... fn. If a list is also specified in the SELECT clause, the column references 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.2001 to 05.03.2001, sorted by plane type and number of occupied seats:

DATA: wa_sflight TYPE sflight.

SELECT * FROM sflight INTO wa_sflight

WHERE carrid = 'LH' AND

fldate BETWEEN '20010227' AND '20010305'

ORDER BY planetype ASCENDING seatsocc DESCENDING.

WRITE: / wa_sflight-planetype, wa_sflight-seatsocc,

wa_sflight-connid, wa_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.

The columns f1, ..., fn must not be of the type STRING or RAWSTRING.

Notes

Performance:

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 application 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 one of the indexes.

Variant 3

... ORDER BY (source_text)

Effect

Works like ORDER BY f1 ... fn if the variable source_text contains the list f1 ... fn as ABAP source text.

Note

The same restrictions apply to this variant as to ORDER BY f1 ... fn.

Example

After you enter 'cityfrom' or 'cityto', the system outputs all departure or destination cities of Lufthansa, including the number of destinations.

PARAMETERS: comp(80).

DATA: dref TYPE REF TO DATA,

long_name TYPE STRING,

name TYPE STRING,

ftab TYPE TABLE OF STRING,

count TYPE I.

FIELD-SYMBOLS: <fs>.

name = 'SPFLI'.

CONCATENATE name '-' comp INTO long_name.

CREATE DATA dref TYPE (long_name).

ASSIGN dref->* TO <fs>.

APPEND comp TO ftab.

APPEND 'COUNT( * ) AS COUNT' TO ftab.

SELECT DISTINCT (ftab)

INTO (<fs>, count)

FROM (name)

WHERE

carrid = 'LH'

GROUP BY (comp)

ORDER BY (comp).

WRITE: / <fs>, count.

ENDSELECT.

Additional help

Specifying a Sort Sequence

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:05:46 GMT

I am trying to sort by sbook-name, this is my code and doe snot seem to work

Select * from sbook

where

connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate.

order by sbook-name.

select single * from scustom

where id = sbook-customid.

write:/ sbook-bookid, 18 sbook-customid, 40 scustom-name.

endselect.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:07:47 GMT

try this , change the one in bold

Select * from sbook
where
connid = sflight-connid and
carrid = sflight-carrid and
fldate = sflight-fldate.
<b>order by name.</b>
select single * from scustom
where id = sbook-customid.

Message was edited by: Chandrasekhar Jagarlamudi

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:08:42 GMT

Try this:

Select * from sbook

where

connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate.

order by name.

You do not need to specify the field name as sbook-name.

Hope this helps.

Sudha

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:11:36 GMT

Hi,

even SELECT allows us to specify sorting order, but we should NOT do that, because of performance reasons. it takes time to sort the data. instead you select all your records to ur internal table by SELECT & then use

SORT ITAB BY <FIELD1> <FIELD2> ASCENDING/DESCENDING.

Regards,

Srikanth.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:12:06 GMT

I have changed to code to as suggested, however i think because it is a nested select, i am getting and error: Statement "ORDER" is not defined. Check your spelling.

Select * from sbook

where

connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate.

order by name.

select single * from scustom

where id = sbook-customid.

write:...

endselect.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:14:10 GMT

before order by name there is a full stop , pls remove that

Select * from sbook

where

connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate "remove full stop in this line

order by name.

select single * from scustom

where id = sbook-customid.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:18:00 GMT

nope still does not work, the field name actually comes from the single selection from scustom table?

code now..

Select * from sbook

where

connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate.

select single * from scustom

where id = sbook-customid

order by name.

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:25:42 GMT

hi,

i believe,we can use ORDER BY if we are fetching large no of records into an internal table. i dont think its worth in selecting records 1 by 1 using select & endselect.

for performance reasons, you should not use NESTED SELECTS. instead use for all entries which will be faster.

regards

srikanth

Re: Sorting Lists

Former Member — Mon, 12 Jun 2006 09:31:28 GMT

Select * from sbook

where

connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate.

order by name.

select single * from scustom

where id = sbook-customid.

write:...

endselect.

instead of above 2 selects we can write :

Select field1

field2 from sbook

into table it_sbook

where connid = sflight-connid and

carrid = sflight-carrid and

fldate = sflight-fldate.

select * from scustom

into table it_scustom

for all entries in it_sbook

where id = it_sbook-customid.

now you have print these values.

LOOP AT it_sbook.

READ TABLE IT_SCUSTOM WITH KEY ID = it_sbook-CUSTOMID.

*--use READ table,instead of select single.

IF SY-SUBRC = 0.

WRITE 😕

ENDIF.

ENDLOOP.