Order clauseOfficial Content

Allows you to indicate the order in which the query will be returned. It applies to For Each commandData provider groups, Data Selectors and grids.

Syntax

{{order <attributeList>i [ when <cond>]} |  {order none [ when <cond>]}} ...

where:

<attributeList>i

It is a list of attributes separated by comma; each mentioned attribute must be a knowledge base attribute that may be written with or without round brackets. When an order attribute appears between round brackets, it indicates a descending order for this attribute.

Here you can mention extended table attributes and formula attributes. However, if you're working in a centralized platform you can only use the attributes stored in the for each Base Table. Formula attributes need to be evaluated in the server.

<cond>i

For centralized platforms, you can specify one order clause at the most, with no condition ('when'). For client/server platforms, you can define several conditional order clauses, and one unconditional order clause, which should be the last one listed; the reason for this is that as only one of these order clauses will take effect, their conditions (the when conditions) are evaluated one by one until the first True one is reached, which is the one that will be chosen. If none of them are found to be True and there is an unconditional clause (that is, one without a 'when'), the order taken will be that of the unconditional clause. If there is no such clause, the order will be undefined, which means that it will depend on the platform, and it may even vary from one execution to the next. The reason for writing conditional order clauses is motivated by query optimization.

order none

For cases in which we are not interested in a particular order and want it to remain undefined.

Do Order clauses determine the exact order really used by the DBMS in order to solve the query?

The final order that will be generated could be slightly different from that specified by the user, because other considerations are taken into account by the GeneXus Specifier in order to optimize the database access. 

 Basically, the following information is considered to generate the order clause (the low level specification in the code):

  • Attributes of the order clause specified by the user.
  • Restrictions that apply to the level: attributes instanced by parameter, attributes instanced in the context (such as higher level for eachs, groups, grids), explicit conditions (such as 'where' conditions when for eachs or groups, or 'conditions' properties when grids or data selectors or 'conditions' selector when general conditions).
  • Existence of indexes on such attributes.

In summary, with the order clause, the developer indicates the order in which he or she wants records to be processed and retrieved, and they will. But to perform the actual processing, the Specifier could alter that clause, supplemented with contextual info (if there exists defined indexes, conditions for equality, etc) in order to be more performant, although DBMS itself ends up being the one who decides the execution plan. Nevertheless, it is important to understand the data will be retrieved in the explicit order. See the Order Clause Specification for more details.

Centralized Platforms

The above considerations do not necessarily apply in centralized platforms. In those cases, GeneXus searches for an index in the level's base table considering the attribute list. If it does not exist, a temporary index will be created every time the program is executed. As temporary index creation may be too slow in some cases, this is when the creation of a user index is recommended.

 If no order is specified:

  • If level is nested into another level: GeneXus will try to find the index that matches all outer orders, optimizing performance.
  • Otherwise: the Primary Key of the Base Table will be chosen.

Note

As of GeneXus Tilo, the order keyword cannot be ommited in the order clause. See Base Transaction in For Each command for more information.