Order-by clause

The order in which the return clause of a FLWOR expression is evaluated with the appropriate variable allocation can be explicitly enforced by the sorting clause order by. The relative order of two conditions of variable allocations is determined by the order statement. For example, the order by clause enforces in the following statement the order as it would be implicitly given when interchanging the for clauses:

In the case of character strings, a sorting order ("collation") can be additionally indicated. Details on the sorting on character strings can be found in the description of the XQuery character string functions. In the following example the medication is outputted with regard to the US-American collation, based on the designation of the medication; same medication are sorted in descending order by their prices:

The stable order by variant of the normal order by clause is then taking effect if two items cannot be clearly distinguished regarding the preset sorting criterion. While in the stable variant the adherence of the order of the elements with respect to the output document is enforced, the order in the normal variant of the order by clause is in this case independent of the implementation. In the following example the order of the employees is adopted from the original document, if they have their birthday on the same day:

With regard to the execution order of for statements, the following FLWOR expression returns the same result as the above statement with the sorting criterion by $i , $j:

Besides the ascending and the descending sorting, empty greatest or empty least can be indicated as a further sorting modifier, whereby the sorting semantics is clearly regulated, especially with empty sequences and NaN values. If the empty_least modifier is indicated, xi is sorted before xj in case xi represents an empty and xj a non-empty sequence or in case xi represents the value NaN and xj is neither NaN nor an empty sequence.

In case of character strings, the same result occurs if the character string function fn:compare(xi, xj, c) with explicit sorting order defined by the c collation returns a value greater than 0 or if the normal comparison xi < xj evaluates to true. The opposite applies in case the inverse empty greatest modifier is indicated. If the explicit indication of such a modifier is missing, the implementation decides which of the two rules applies in case of conflict. The following example sorts medications by their prices, whereby missing price indications are semantically interpreted as the smallest possible price and (conditioned by the descending sorting) appear at the end in the medication list.

Source: "XQuery – Grundlagen und fortgeschrittene Methoden", dpunkt-Verlag, Heidelberg (2004)