Select … From … Order By…

Relational model versus Semantic arrays dml

The Select … From … Order By statement is used to select one or more attributes from a table with an Order By Clause (sort criterium).

single attribute ordering

Assume the following SQL Statement:

Select Street, Number, Zipcode, Town From Appartment Order By ZipCode

This statement can be applied on our relation model, resulting in the following data:

The GeoDMS works with semantic arrays, in which the sequence of elements matter. Therefore, for a SQL Statement with an Order By clause, in the GeoDMS a new domain-unit needs to be configured. The new domain unit has the same cardinality as the source domain and is often configured with the range function (see the example).

For the source domain an index attribute need to be configured with the index function, with the attribute to be sorted as argument. In the example the index attribute is called ZipOrderAtt and results in the index-numbers of the source domain unit in the sorted order of the ZipCodes.

The index attribute is used in the lookup functions for each requested attribute to get the elements in the new order. The lookup results have the same domain as the source domain, with the union_data function the data is converted to the new domain (ZipOrder).

GeoDMS configuration (the Appartment domain unit is configured in a src container):

attribute<src/Appartment> ZipOrderAtt (src/Appartment) := index(src/Appartment/ZipCode);

unit<uint32> ZipOrder := unique(ZipOrderAtt)
{
   attribute<string> Street  := (src/Apartment/Street[ZipOrderAtt])[Values];
   attribute<uint32> Number  := (src/Apartment/Number[ZipOrderAtt])[Values];
   attribute<string> ZipCode := (src/Apartment/ZipCode[ZipOrderAtt])[Values];
   attribute<string> Town    := (src/Apartment/Town[ZipOrderAtt])[Values];
}

The resulting domain is sorted by ZipCode. For elements with the same ZipCode, the order in the source domain is maintained.

multiple attributes ordering

Assume the following SQL Statement:

Select Street, Number, Zipcode, Town From Appartment Order By ZipCode, Number

The GeoDMS configuration is similar to the single attribute ordering example, with one exception. The index function in the GeoDMS does not support multiple arguments. Therefore concatenate the ZipCode and Number attributes as strings, see the example:

attribute<src/Appartment> ZipNumberOrderAtt (src/Appartment) := 
   index(src/Appartment/ZipCode + '_' + string(src/Appartment/Number));

This ZipNumberOrderAtt attribute can now be used in the lookup functions in the same way as the ZipOrderAtt in the single attribute ordering example.