order by clauses

An order by clause in a FLWOR expression specifies the order in which values are processed by the return clause. If no order by clause is present, the results of a FLWOR expression are returned in a non-deterministic order.

An order by clause contains one or more ordering specifications. Ordering specifications are used to reorder the tuples of variable bindings that are retained after being filtered by the where clause. The resulting order determines the order in which the return clause is evaluated.

Each ordering specification consists of an expression, which is evaluated to produce an ordering key, and an order modifier, which specifies the sort order (ascending or descending) for the ordering keys. The relative order of two tuples is determined by comparing the values of their ordering keys as strings, working from left to right.

In the following example, a FLWOR expression includes an order by clause that sorts products in descending order based on their price:

<price_list>{
    for $prod in db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')/product/description
    order by xs:decimal($prod/price) descending
    return
    <product>{$prod/name, $prod/price}</product>}
</price_list>

During processing of the order by clause, the expression in the ordering specification is evaluated for each tuple that is generated by the for clause. For the first tuple, the value that is returned by the expression xs:decimal($prod/price) is 9.99. The expression is then evaluated for the next tuple, and the expression returns the value 19.99. Because the ordering specification indicates that items are sorted in descending order, the product with the price 19.99 sorts before the product with the price 9.99. This sorting process continues until all tuples are reordered. The return clause then executes once for each tuple in the reordered tuple stream.

When run against the PRODUCT.DESCRIPTION table of the SAMPLE database, the query in the example returns the following result:

<price_list>
    <product>
        <name>Snow Shovel, Super Deluxe 26"</name>
        <price>49.99</price>
    </product>
    <product>
        <name>Snow Shovel, Deluxe 24"</name>
        <price>19.99</price></product>
    <product>
        <name>Snow Shovel, Basic 22"</name>
        <price>9.99</price>
    </product>
    <product>
        <name>Ice Scraper, Windshield 4" Wide</name>
        <price>3.99</price>
    </product>
</price_list>

In this example, the expression in the ordering specification constructs an xs:decimal value from the value of the price element. This type conversion is necessary because the type of the price element is xdt:untypedAtomic. Without this conversion, the result would use string ordering rather than numeric ordering.

Tip: You can use an order by clause in a FLWOR expression to specify value ordering in a query that would otherwise not require iteration. For example, the following path expression returns a list of customerinfo elements with a customer ID (Cid) that is greater than 1000:

db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo[@Cid > "1000"]

To return these items in ascending order by the name of the customer, however, you would need to specify a FLWOR expression that includes an order by clause:

for $custinfo in db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo
where ($custinfo/@Cid > "1000")
order by $custinfo/name ascending
return $custinfo

The ordering key does not need be part of the output. The following query produces a list of product names, in descending order by price, but does not include the price in the output:

for $prod in db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')/product
order by xs:decimal($prod/description/price) descending
return $prod/name

Rules for comparing ordering specifications

The process of evaluating and comparing ordering specifications is based on the following rules:

The expression in the ordering specification is evaluated and atomization is applied to the result. The result of atomization must be either a single atomic value or an empty sequence; otherwise an error is returned. The result of evaluating an ordering specification is called an ordering key.
If the type of an ordering key is xdt:untypedAtomic, then that key is cast to the type xs:string. Consistently treating untyped values as strings enables the sorting process to begin without complete knowledge of the types of all of the values to be sorted.
If the values that are generated by an ordering specification are not all of the same type, these values (keys) are converted to a common type by subtype substitution or type promotion. Keys are compared by converting them to the least common type that supports the gt operator. For example, if an ordering specification generates a list of keys that includes both xs:anyURI values and xs:string values, the keys are compared by using the gt operator of the xs:string type. If the ordering keys that are generated by a given ordering specification do not have a common type that supports the gt operator, an error results.
The values of the ordering keys are used to determine the order in which tuples of bound variables are passed to the return clause for execution. The ordering of tuples is determined by comparing their ordering keys, working from left to right, by using the following rules:
- If the sort order is ascending, tuples with ordering keys that are greater than other tuples sort after those tuples.
- If the sort order is descending, tuples with ordering keys that are greater than other tuples sort before those tuples.
The greater-than relationship for ordering keys is defined as follows:
- An empty sequence is greater than all other values.
- NaN is interpreted as greater than all other values except the empty sequence.
- A value is greater than another value if, when the value is compared to another value, the gt operator returns true.
- Neither of the special floating-point values positive zero or negative zero is greater than the other because +0.0 gt -0.0 and -0.0 gt +0.0 are both false.
Note: Tuples whose ordering key is empty appear at the end of the output stream if the ascending option, which is the default, is specified, or at the beginning of the output stream if the descending option is specified.