topic Re: Default sort order in the select statement in Application Development and Automation Discussions

Default sort order in the select statement

Former Member — Tue, 05 Mar 2013 07:34:50 GMT

Greeting everyone.

I am analysing one of the ABAP programs in our sytem.

It seems to be producing different results in development and production system.

I understand that there will be data differences between these two systems. But, the sort order of the results returned by the select statements vary.

When the below SELECT statement is executed, in development system the result is sorted by the database table key FIELD1. But in production system, there is no sorting as far as I can see.

Is there some parameter or database setting that controls this.

How can we get the properly sorted results in our Production system.

Below is my select statement and the associated data declarations

 TYPES: BEGIN OF ty_table1,
                 field2 like table1-field2,
                 field1 like table1-field1,
                 field3 like table1-field3,
                 field4 like table1-field4,
               END OF ty_table1.
DATA: i_table1 TYPE STANDARD TABLE OF ty_table1.
                  
SELECT field2 field1 field3 field4 
               FROM table1
               INTO CORRESPONDING FIELDS OF TABLE i_table1
               WHERE
                    field2 = '100191'.

The database table TABLE1, has the fields in order FIELD1-FIELD2-FIELD3-FIELD4. FIELD1 is the only key field.

Re: Default sort order in the select statement

Former Member — Tue, 05 Mar 2013 08:44:50 GMT

Hi Joshva,

you should guarantee equal sort order by abap coding. I would not use it in the SQL-statement, since ORDER BY is decreasing performance.

Instead declare the internal table as sorted with unique key or you sort your internal standard table as desired by FIELD1 ascending or descending.

Jonas

Re: Default sort order in the select statement

wol — Tue, 05 Mar 2013 09:09:38 GMT

Hello Joshva,

the order in which the select-statement fetches the data if no order is defined is not predictable. It is the order on the db which seems to be the order of creation mostly but it can change at any time.

This is dangerous because the developer sees any order and thinks it is always like this. But it is not!

So don´t consider any order if no order defined in your statement!

Regards

Stefan

Re: Default sort order in the select statement

Former Member — Tue, 05 Mar 2013 09:27:35 GMT

Hi Joshva,

There are 3 things while writing a Select Statement -

1. The order of the fields to be selected should be in the same order as they are in the database table.

2. The order of fields in the where condition should also be in the same order as in the database table.

3. The order of fields in the internal table you are selecting into, should also be in the same order as in the database table.

This internal table ideally should be of standard type while declaring and should be sorted after the select query.

So ideally your Select query should be like this

TYPES : BEGIN OF TY_TABLE1,

FIELD1 TYPE TABLE1-FIELD1,

FIELD2 TYPE TABLE1-FIELD2,

FIELD3 TYPE TABLE1-FIELD3,

FIELD4 TYPE TABLE1-FIELD4,

END OF TY_TABLE1.

DATA : I_TABLE1 TYPE STANDARD TABLE OF TY_TABLE1.

SELECT FIELD1

FIELD2

FIELD3

FIELD4

FROM TABLE1

INTO TABLE I_TABLE1

WHERE FIELD1 =

AND FIELD2 =

AND FIELD3 =

AND FIELD4 =

IF SY-SUBRC EQ 0.

SORT I_TABLE1 BY FIELD1 FIELD2 FIELD3 FIELD4.

ENDIF.

Re: Default sort order in the select statement

Former Member — Tue, 05 Mar 2013 09:33:45 GMT

Thank you very much for your answers.

I will make the changes in ABAP code, to sort the internal table.

I just wanted to check whether there is any way to control it other than, through the program.

But you have clarified this for me.

Regards,

Joshua.

Re: Default sort order in the select statement

RaymondGiuseppi — Tue, 05 Mar 2013 13:41:38 GMT

Yes good practices is always to sort explicitly data (either in SELECT or SORT statement or definition of internal table) that will prevent from discrepancies like .Note 602138 - IDoc: Incorrect sequence of segment fields...

Regards,

Raymond

Re: Default sort order in the select statement

matt — Thu, 06 Jun 2019 08:50:53 GMT

ORDER BY does not decrease performance. If you want data in a specific order, use ORDER BY in the select -> not with SORT statement afterwards.