Default sort order by SELECT with GROUP BY

[michał_badura]

Hello experts!

I'm facing a problem in ABAP SQL, and don't know, whether it's some kind of undocumented feature, or is my database system doing something strange. The issue can be observed by selecting data with GROUP BY addition, but without ORDER BY addition.

Here's my code:

SELECT tabclass,
       MIN( as4date ) AS as4date_min,
       MAX( as4date ) AS as4date_max
  FROM dd02l
  INTO TABLE @DATA(g_dd02l)
  WHERE tabname LIKE 'DD%'
  GROUP BY tabclass.

In my 7.40 systems: SP09 and SP12 with MaxDB the results here are as expected: four lines in alphabetical order: APPEND, INTTAB, POOL (only by SP12), TRANSP, VIEW.

But if I change the code slightly:

SELECT tabclass
  FROM dd02l
  INTO TABLE @DATA(g_dd02l)
  WHERE tabname LIKE 'DD%'
  GROUP BY tabclass.

I get different order, namely POOL (only SP12), VIEW, APPEND, INTTAB, TRANSP. Why?

I have checked the same in customer systems: 7.40 SP14 with DB6, 7.50 SP22 with DB6 and 7.50 SP23 with ORACLE, and there is no difference between those two codes.

On the other hand, in customer system 7.52 SP09 with MaxDB the results are same as in my both systems. I thought, it would be a MaxDB issue. But then I came across customer system 7.31 SP29 with DB400. Here the results, for both codes, are as follows: VIEW, INTTAB, APPEND, POOL, TRANSP.

What is the reason for this sort order? Maybe the sort order by using GROUP BY is not given, just like by DISTINCT. But by DISTINCT it's explicitly documented, and by GROUP BY ìt's not. Why does the sort order depend on whether aggregate functions are used or not? Any clues?

[Sandra_Rossi]

In SQL, GROUP BY doesn't mean sorting although you may think so (because very often the database extract data by using a sorted index). You always need to indicate ORDER BY. See Dominik answer for more information.

[DominikTylczyn]

Hello michalbadura

As per documentation to SELECT - ORDER BY

If the addition ORDER BY is not specified, the order of all the columns in the results set is undefined.

You are not using ORDER BY hence the order of the result is undefined, i.e. it can be different depending on database, number of calls etc. You must not rely on the order of the result if you do not specify ORDER BY.

Best regards

Dominik Tylczynski

[michał_badura]

Hi dominik-tylczynski, thank You for Your answer.

Please don't misunderstand me - I think You're right. But on the other hand it somehow does not fit to the rest of the pieces. What I mean:

• The statement You quote appeared in the documentation with release 7.40. By that time HANA was already there, which is known for its issues with missing ORDER BY. Maybe earlier the same was stated implicitly - documentation for release 7.31 states: The order of the rows in the result set refers to all columns that are not listed after ORDER BY, is undefined (...). If there is no ORDER BY at all, then no column is listed after it, and so the order is undefined for each column.
• If missing ORDER BY always leads to an undefined sort order, then why did the documentation for DISTINCT stated till 7.40: If the addition ORDER BY is not used, the order of the rows in the results set is not defined and, if the same statement SELECT is executed multiple times, the order may be different each time. It would be then superfluous.
• If the sort order is not given without ORDER BY for every DBMS, then why is SAP warning about missing ORDER BY only by HANA, e.g. ATC/CI check variant FUNCTIONAL_DB.