Incorrect sort order in SE16

[luis_rod]

Hi all,

I have a table that does display an incorrect sort order when queried with SE16n.

The tables has the following columns:

MANDT
ID (INT2 - Key)
TEXT (CHAR80 - Non key)

Even though the data was entered in order (ie. ID = 1, 2, 3, etc) and shows that way when checked with SM30 or with SQL , when I query it using SE16 it sorts by the TEXT column.

Any ideas why this happen? I thought that the default order of SE16 was by the table key...

Thanks beforehand,

Luis

[BiberM]

Just a short question: Are you using a SAP HANA as database? This Database does not automatically sort the data by primary key. I am currently unsure if the SE16 or SE16N are sorting themselfes. Data preview and SQL console in Eclipse does certainly not.

Another reason could be numeric types of the IDs. In that case they are sorted alphabetically (1, 10, 11, [..], 2, 20, ...).

Another place where the entries are not sorted for sure is SM30.

[luis_rod]

Michael,

No, We are not using HANA. I understand perfectly that, by default, SQL queries are not ordered, unless one uses the ORDER BY clause. My question was because I thought that SE16n sorted by the primary key. In this case, the results are ordered by the second column (TEXT) which is NOT a key field. When using SM30 for this table the results are shown ordered by key.

Regards,

Luis

[BiberM]

Did someone change default display variants?

Does that happen in all systems in your landscape or only in this particular?

I'm also interested in the solution to this. If you happen to find a solution please share it here.

[matt]

There is no defined order on a table in any relational database. If you want an order, you must sort. This is not new with HANA. I've seen it in Oracle, Informix, Ingress... (yes, I'm old).

[Sandra_Rossi]

SQL doesn't sort the result of SELECT by default. You must use ORDER BY to do that. BUT it may impair the performance. So, unless there's a requirement to sort, the rows won't be sorted. I don't think there's an added value to sort rows in SE16/SE16N.

Customizing tables are usually very small. It doesn't surprise me that the lines are sorted, that won't impair the performance significantly.

[michael_piesche]

SE16 and SE16N do not sort by default, just as no RDBMS sorts by default. Only when a sort order is explicitly set, sorting will take place.

You mentioned SE16 with that error but also SE16N, do you actually have the same behavior with those or different?

My first guess is, that the table is buffered, which will in most cases lead to other 'sorting' then when the data comes 'unsorted' from the DB. Can you check the buffer settings for your table and also compare the calls with SE16 and SE16N.

• SE16N always surpasses the buffer, whereas SE16 doesnt and it even generates for each table its own display report. I assume SM30 does not bypass the buffer, but since reports for SM30 are generated as well, there might be also more logic going on. And the SQL editor will definitly not sort by default.

If you want to compare the SE16 SQL select statement with the select statement in SE16N, SM30 or SQL editor, I would recommend you compare the ST05 SQL traces (or ST01 if you want to).

• You could also debug the SAP coding when the select statement is called and compare the order of the selected data in case comparing SQL traces is too troublesome:
• SE16 generated reports look most likely like this: /1BCDWB/DBMARA in case of MARA or /1BCDWB/DBMVKE in case of MVKE and they have a couple predefined SQL selects for the db, depending on the last config regarding selection and output criteria
• The select statement for SM30 can be found with the "Where Used List" or from SE11 and through the Table Maintenance Generator where the function group is set
• SE16N builds the select statement dynamically in Include LSE16NF95 - Form SELECT_CENTRAL

If the SQL statements are the same, then the difference can only be explained by the program handling the selected data. In that case, I would assume a set layout or the use of a variant.

[michael_piesche]

377cae7333c044888184eb2537b4355e, please follow up on your open question.

• comment answers or your question if there are still open issues.
• otherwise mark an answer as accepted if it helped you solve your problem
• or post an answer of yourself and accept it if you found another useful solution yourself
• or redirect your question to another question that is related and was useful to solve your problem
• in the end, close your question