Default sort order in the select statement

[Former Member]

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.

[Former Member]

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

[matt]

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.

[wol]

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

[former_member946717]

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.

[Former Member]

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.

[raymond_giuseppi]

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