Order specification
Operands in the operand list may be attribute names or property paths referring to attributes defined in the calling object data type, i.e. in the VIEW attribute list or SELECT operand list. Order attributes usually refer to elementary data types, but may also refer to complex data types, operations or user-defined enumerations. In order to call order functions in an operation path, ORDER should be used instead of ORDER BY.
The ORDER BY operation also allows ordering collections by a key, in which case a key name must be passed in the operand list (single operand). By preceding order attributes with names (e.g. (age = age_group), one may explicitly assign attribute names, which, however, do not affect the output instances..
In addition, each order operand may be extended by attribute options:
- IC | IGNORE_CASE: use case-insensitive compare for key component
- DESC | DESCENDING: order component values in descending order
- NOT_EMPTY: suppress empty key values (not on component level but for whole key)
- UNIQUE: create index with unique keys. Duplicate key values will cause an exception.
The last two options are not considered as column options and apply on the index when being defined ones.
// simple order by stored and calculated attribute values
ORDER BY(age,sex)
ORDER BY(string age_group = (age < 20 ? 'young' :
age < 60 ? 'middle':
'old' ) DESC,sex)
// call order in an operation path
person.orderBy(age,first_name IC)
person.orderBy(string age_group = (age < 20 ? 'young' :
age < 50 ? 'middle':
'old' ),sex)
// call order in a C++/Java/C# program
person.orderBy("age,sex");
person.orderBy("string age_group = (age < 20 ? 'young' :
age < 50 ? 'middle':
'old' ),sex");
order_spec := _order_by operand_list