SELECT statement INTO TABLE - what determines order of entries in in.table?

[Former Member]

Hello everyone.

I habe a question: A select statement selects multiple entries from a data base table that are put into an internal table.

The question : What determines the order of the entries in the internal table?

And what might change the order all of a suddden in that internal table?

Could reorg. activities/archiving (even ongoing) cause any different result in the order as before ?

I do not mean different entries but I rather refer to the sorting of the internal table, if it is not explicitly stated in the select or in parts of the coding.

Any explaination is appreciated!

Thanks!

CN.

[Former Member]

Hi,

By default the entries are sorted by the primary key (if you don't specify the ORDER BY clause ) .

Therefore, if you change the order of the primary key, or add/remove a key field , will affect the selection and filling of entries into the internal table.

regards,

Advait.

[Former Member]

>

> Hi,
> By default the entries are sorted by the primary key (if you don't specify the ORDER BY clause ) . 
> Therefore, if you change the order of the primary key, or add/remove a key field , will affect the selection and filling of entries into the internal table.
> 
> regards,
> Advait.

Hi Advait,

thanks for your reply. Are you sure that the table is ALWAYS sorted by using the primary key? A change of the primary will usually change the resulting table as well as mostly requiring a change of the WHERE clause. ;-)!!

Thanks!

CN.

[Former Member]

Hi,

Its not the order in which you specify the fields in the where clause that affects the sequence of the selection.

What I said is that by default the entries are sorted by the primary key of the table.

I did a bit of a test out of curiosity and after seeing so many different replies on the thread. I was surprised to see the results of the different scenarios.

REPORT  Z_TEST_SELECTION                        .

tables vbap.

data it_vbap type table of vbap.
data it_vbak type table of vbak.

select-options s_vbeln for vbap-vbeln.
select-options s_posnr for vbap-posnr.
"======================================================================
"s_vbeln has following entries
" 5
" 6
" 1
" 2
"s_posnr has following entries.
" 10

" Sorts in ascending order by VBELN and POSNR by default

select * up to 20 rows from vbap into table it_vbap.

refresh it_vbap.

"Below two cases are absolutely unreliable and I would suggest that a 
"sort should be mentioned after these statements. So when we use select 
"options specify values with the in clause, the sort is not reliable.

"Strangely , neither the sequence is not determined by the order in
"which the data was entered in the select option nor in the sequence of
"the primary key. And this is still a mystery to me about the order in which 
"the entries are selected. 

" Any answeres anyone ?
"1. 
select * from vbap into table it_vbap
where  vbeln in ('0000000002','0000000006','0000000005','0000000001')
and posnr in s_posnr.
* Resulted in
*900  |0000000001|000010|
*900  |0000000005|000010|
*900  |0000000006|000010|
*900  |0000000002|000010|
"=========================================================
"2. 
select * from vbap into table it_vbap
where  posnr in s_posnr
and    vbeln in s_vbeln.
* Resulted in
*900  |0000000002|000010|
*900  |0000000001|000010|
*900  |0000000005|000010|
*900  |0000000006|000010|

refresh it_vbap.

"Here the orders were selected in Decending order of Sales order, however
"the posnr is not mentioned in the order by clause, hence posnr was
"still in ascending order.

select * from vbap into table it_vbap
where posnr in s_posnr and vbeln in s_vbeln
order by vbeln descending.

write : 'done!'.

refresh it_vbap.

"Here the orders were selected in Descending order of Sales order.
select * from vbak into table it_vbak where vbeln in s_vbeln
order by vbeln descending.

" The entries were selected in the ascending order of the key VBELN and
" POSNR, so it seems that for all entries does not affect the selection of records.
select * from vbap into table it_vbap
for all entries in it_vbak
where vbeln = it_vbak-vbeln
and   posnr in s_posnr.

Of course if you change the order of the primary key (in the transparent table, not in the where clause), the entries will be sorted as per the changed key.

But we seldom change the the sequence of the primary key. In most cases we normally add a new field and make it a key field in addition to the existing key fields.

I hope this helps you.

There might be many more cases and examples than given above, which you can try and reply on this post. Lets see what we come up with.

regards,

Advait

[Pawan_Kesari]

by default it is sorted on primary key of the table from where the data is coming, however in following scrnario it can be different.

1. by using ORDER BY clause.

2. FOR ALL ENTRIES - order of data depend on the order in internal table used for for all entries.

3. If SELECT statement use different index (other than primary key) then order will be same as fields define in that index.

[Former Member]

>

> 2. FOR ALL ENTRIES - order of data depend on the order in internal table used for for all entries.

Hello Pawan,

is that really true? Does the order of the 'FOR ALL ENTRIES' table determine the order of the entry provided in the resulting table?

CN.

[rainer_hbenthal]

>

> The question : What determines the order of the entries in the internal table?  

Even if the result seems to be ordered, you have to take them as unordered unless you specify a order by clause.

The ordering by PK is not a determined feature. It can vary from reorg to reorg, resultiung in a different access path, or the result resides in the buffers.

[Former Member]

>

> The ordering by PK is not a determined feature. It can vary from reorg to reorg, resultiung in a different access path, or the result resides in the buffers.

Thanks Rainer.

What do you mean by PK?

If I understand you correctly, the order of an 'INTO TABLE' table is rather incidental then a given.

How can a reorg cause such issue that a table which used to come along sorted all of a sudden is not sorted anymore?

Is it compulsory to include an explicit SORT (either by sorting the internal table or by the order by clause..I prefer to leave to the application server) before e.g. to use a 'READ BINARY' ?

Thanks.

CN.

[naimesh_patel]

Yes. It is mandatory to SORT the internal table before using the READ ... BINARY KEY.

We must not depend on the Sorting sequence in which data has been selected.

Regards,

Naimesh Patel

[Former Member]

Hi Patel,

very well - that is what I think, too. I understand that a binary read depending on the 'KEY' it is looking for requires a certain sort.

I just want to understand why a table which came along sorted from a selection statement is all of sudden 'out of order'.The funny thing is that the particular table which was all sorted previously comes along unsorted but the 'unsorted' order stays intact until the next...

What are the possible causes for a sudden change in the sorting?

Index creation (although definitely not used in select) ? Reorg on the database ?

Different data base releases?

Competing tables accesses?

To be save it can be said that a sort is always necessary - to be on the safe side.

Thanks!

CN.