Does "ORDER BY PRIMARY KEY" make any sense inside SELECT..ENDSELECT in HANA?

[Former Member]

Hello All,

I try to understand if there is any difference in result between a) and b) in HANA:

a)
    SELECT * FROM table INTO work_area
      WHERE field = field
      ORDER BY PRIMARY KEY.
    ENDSELECT.

b)
    SELECT * FROM table INTO work_area
      WHERE field = field
    ENDSELECT.

In other words - does "ORDER BY PRIMARY KEY" sort the result only (not the DB table before the SELECT...) ?

Thanks, Alex.

[Former Member]

Hi Alexander,

from my understanding of the columnstore and the "ORDER BY PRIMARY KEY" is as follows:

Order by Primary Key, does not sort the results, it fetches the data sorted as they are sorted on the database according to the primary key, if the database is sorting the table, if not, the results are sorted according to the primary keys.

As a database using columnstore you can not just fetch the data and have them sorted by primary key, so the openSQL syntax is probably emulated and so the results are sorted.

TL:DR on HANA the ORDER BY PRIMARY KEY should not be faster than a manual ORDER BY "keys" due to emulation

You can also read the following link for general information.

Specifying a Sort Order - ABAP Programming (BC-ABA) - SAP Library

[matt]

Not the same. If, for example, you use MARA, the first one is identical to

    SELECT * FROM mara INTO work_area

      WHERE field = field

      ORDER BY matnr.

    ENDSELECT.

The second one has no defined order. I can't see why this would be any different in HANA. The underlying mechanism may be different. The performance may vary according to database.

If you get different results according to your database, that would make a mockery of open SQL.

[Former Member]

Hi Matthew,

I try to understand it... the following SELECT...ENDSELECT brings 1 record as a result only, I mean, what exactly will be sorted if result is just 1 record?

    SELECT * FROM mara INTO work_area

      WHERE field = field

      ORDER BY matnr.

    ENDSELECT.

Thanks.

[matt]

If I understand you correctly, then if you write something like this all should become clear:

    SELECT * FROM mara INTO work_area

      WHERE field = field

      ORDER BY matnr.

      WRITE / work_area-matnr.

    ENDSELECT.

[Former Member]

OK, this makes all clear: ORDER BY sorts the content of a table before data from table is selected, that means ORDER BY inside SELECT..ENDSELECT can give not just another way sorted lines, but even completelly different result, for example in such case:

SELECT * FROM vbrk UP TO 10 ROWS
  ORDER BY fkart.
  WRITE vbrk-vbeln.
ENDSELECT.

subject closed.