Using multiple ordering specifications

In order to sort on more than one expression, you can include multiple ordering specifications, as shown in Example 7-2.

for $item in doc("order.xml")//item
order by $item/@dept, $item/@num
return $item

This sorts the results first by department, then by item number. An unlimited number of ordering specifications can be included.

Sorting and types

When sorting values, the processor considers their type. All the values returned by a single ordering specification expression must have comparable types. For example, they could be all xs:integer or all xs:string. They could also be a mix of xs:integer and xs:decimal, since values of these two types can be compared.

However, if integer values are mixed with string values, type error XPTY0004 is raised. It is acceptable, of course, for different ordering specifications to sort on values of different types; in Example 7-2, item numbers could be integers while departments are strings.

Untyped values are treated like strings. If your values are untyped but you want them to be treated as numeric for sorting purposes, you can use the number function, as in:

order by number($item/@num)

This allows the untyped value 10 to come after the untyped value 9. If they were treated as strings, the value 10 would come before 9.

Order modifiers

Several order modifiers can optionally be specified for each ordering specification:

• ascending and descending specify the sort direction. The default is ascending.

• empty greatest and empty least specify how to sort the empty sequence.

• collation, followed by a collation URI in quotes, specifies a collation used to determine the sort order of strings. Collations are described in detail in “Collations”.

The syntax of an order modifier is shown in Figure 7-2.

Figure 7-2. Syntax of an order modifier

Order modifiers apply to only one order specification. For example, if you specify:

order by $item/@dept, $item/@num descending

the descending modifier applies only to $item/@num, not to $item/@dept. If you want both to be sorted in descending order, you have to specify:

order by $item/@dept descending, $item/@num descending

Empty order

The order modifiers empty greatest and empty least indicate whether the empty sequence and NaN should be considered a low value or a high value. If empty greatest is specified, the empty sequence is greater than NaN, and NaN is greater than all other values. If empty least is specified, the opposite is true; the empty sequence is less than NaN, and NaN is less than all other values. Note that this applies to the empty sequence and NaN only, not to zero-length strings, which are always sorted before other strings.

You can also specify the default behavior for all order by clauses in the query prolog, using an empty order declaration, whose syntax is shown in Figure 7-3.

Figure 7-3. Syntax of an empty order declaration

Example 7-3 shows a query that uses an empty order declaration and sorts the results by the color attributes. Because the greatest option is chosen, the items with no color attribute appear last in the results.

declare default order empty greatest;
for $item in doc("order.xml")//item
order by $item/@color
return $item

<item dept="WMN" num="557" quantity="1" color="black"/>
<item dept="MEN" num="784" quantity="1" color="gray"/>
<item dept="WMN" num="557" quantity="1" color="navy"/>
<item dept="MEN" num="784" quantity="1" color="white"/>
<item dept="ACC" num="563" quantity="1"/>
<item dept="ACC" num="443" quantity="2"/>

The setting in the empty order declaration applies unless it is overridden by an order modifier in an individual ordering specification. The empty order declaration in the prolog applies only when an order by clause is present; otherwise, the results are not sorted. If no empty order declaration is present, the default order for empty sequences is implementation-defined.

Stable ordering

When you sort on $item/@num, several values may be returned that have the same sort value. If stable ordering is not in use, the implementation is free to return those values that have equal sort values in any order. If you want those with equal sort values to be sorted in the order of the input sequence, or if you simply want to ensure that every implementation returns the values in the same order for the query, you can use the stable keyword before the keywords order by. For example, if you specify:

stable order by $item/@num

the items with the same num value are always returned in the order returned by the for expression, within the sorted results.

More complex order specifications

So far, the order specifications have been simple path expressions. You can sort based on almost any expression, as long as it only returns a single item. For example, you could sort on the result of a function call, such as:

order by substring($item/@dept, 2, 2)

which sorts on a substring of the department, or you could sort on a conditional expression, as in:

order by (if ($item/@color) then $item/@color else "unknown")

which sorts on the color if it exists or the string unknown if it does not. In addition, you could use a path expression that refers to a completely different XML document, as in:

order by doc("catalog.xml")//product[number = $item/@num]/name

which orders the results based on a name it looks up in the catalog.xml document.

A common requirement is to parameterize the sort key—that is, to decide at runtime what sort key to use. In some cases you can use:

order by $item/@*[name()=$param]

In other cases you may need to use an extension function, as described in “Dynamic Paths”.

Document order defined

The document order of a set of nodes is:

• The document node itself

• Each element node in order of the appearance of its start tag, followed by:

  • Its attribute nodes, in an implementation-dependent order

  • Its children (text nodes, child elements, comments, and processing instructions) in the order they appear

If a sequence containing nodes from more than one document is sorted in document order, it is arbitrary (implementation-dependent) which document comes first, but all the nodes from one document come before all the nodes from the other document. For nodes that are not part of a document, such as those that are constructed in your query, the order is implementation-dependent, but stable.

There is no such thing as a document order on atomic values.

Sorting in document order

Certain kinds of expressions, including path expressions and operators that combine sequences (|, union, intersect, and except), eliminate duplicate nodes and return nodes in document order automatically. For example, the path expression:

doc("catalog.xml")//product/(number | name)

retrieves the number and name children of product, in document order. If you want all the number children to appear before all the name children, you need to use a sequence constructor, as in:

(doc("catalog.xml")//product/number, doc("catalog.xml")//product/name)

which uses parentheses and a comma. This sequence constructor maintains the order of the items, putting all the results of the first expression first in the sequence, and all the results of the second expression next.

If you have a sequence of nodes that are not in document order, but you want them to be, you can simply use the expression:

$mySequence/.

where $mySequence is a sequence of nodes. The / operator means that it is a path expression, which always returns nodes in document order.

Inadvertent re-sorting in document order

If you have used an order by clause to sort the results of a FLWOR, you should use caution when using the resulting sequence in another expression because the results may be re-sorted to document order. The example shown in Example 7-4 first sorts the products in order by product number, then returns their names in li elements.

let $sortedProds := for $prod in doc("catalog.xml")//product
                    order by $prod/number
                    return $prod
for $prodName in $sortedProds/name
return <li>{string($prodName)}</li>

However, this query returns the products in document order, not product number order. This is because the expression $sortedProds/name re-sorts the nodes back to document order. In this case, the expression can easily be rewritten as shown in Example 7-5. In more complex queries, the error might be more subtle.

for $prod in doc("catalog.xml")//product
order by $prod/number
return <li>{string($prod/name)}</li>

The unordered function

A query author can tell the processor that order does not matter for an individual expression by enclosing it in a call to the unordered function, as shown in Example 7-7. The unordered function takes as an argument any sequence of items, and returns those same items in an undetermined order. Rather than being a function that performs some operation on its argument, it is more a signal to the processor to evaluate the expression without regard to order.

unordered(
for $item in doc("order.xml")//item,
    $prod in doc("catalog.xml")//product
where $item/@num = $prod/number
return <item number="{$item/@num}"
             name="{$prod/name}"
             quantity="{$item/@quantity}"/>
)

The unordered expression

An unordered expression is similar to a call to the unordered function, except that it affects not just the main expression passed as an argument, but also every embedded expression. The syntax of an unordered expression is similar, but it uses curly braces instead of the parentheses, as shown in Example 7-8.

unordered {
for $item in doc("order.xml")//item,
    $prod in doc("catalog.xml")//product
where $item/@num = $prod/number
return <item number="{$item/@num}"
             name="{$prod/name}"
             quantity="{$item/@quantity}"/>
}

Similarly, an ordered expression will allow you to specify that order matters in a certain section of your query. This is generally unnecessary except to override an ordering mode declaration, as described in the next section.

The ordering mode declaration

You can specify whether order is significant for an entire query in the query prolog, using an ordering mode declaration, whose syntax is shown in Figure 7-4.

Figure 7-4. Syntax of an ordering mode declaration

For example, the prolog declaration:

declare ordering unordered;

allows the processor to disregard order for the scope of the entire query, unless it is overridden by an ordered expression or an order by clause. If no ordering mode declaration is present, the default is ordered.

Grouping on multiple values

In order to group on more than one expression, you can include multiple grouping specifications, separated by commas, as shown in Example 7-11.

xquery version "3.0";
for $item in doc("order.xml")//item
group by $d:= $item/@dept, $n:= $item/@num
return <group dept="{$d}" num="{$n}" count="{count($item)}"/>

<group dept="ACC" num="563" count="1"/>
<group dept="MEN" num="784" count="2"/>
<group dept="WMN" num="557" count="2"/>
<group dept="ACC" num="443" count="1"/>

This groups the results both by department and item number. An unlimited number of grouping specifications can be included. To have nested groups, where the groups of numbers appear within the groups of departments, you would need to use a FLWOR embedded within another FLWOR, as shown in Example 7-12.

xquery version "3.0";
for $item in doc("order.xml")//item
group by $d:= $item/@dept
return <group dept="{$d}" count="{count($item)}">{
        for $item-in-dept in $item
        group by $n := $item-in-dept/@num
        return <subgroup num="{$n}" count="{count($item-in-dept)}"/>
      }</group>

<group dept="ACC" count="2">
   <subgroup num="443" count="1"/>
   <subgroup num="563" count="1"/>
</group>
<group dept="MEN" count="2">
   <subgroup num="784" count="2"/>
</group>
<group dept="WMN" count="2">
   <subgroup num="557" count="2"/>
</group>

More complex grouping specifications

The previous examples show grouping on individual values that appear in the input document. However, because you can use any expression to bind a value to a grouping variable, you can write more sophisticated grouping specifications. Example 7-13 shows how you can group the items based on whether the product number is over 500. The grouping variable $g is bound to a Boolean value indicating whether the product number is over 500.

xquery version "3.0";
for $item in doc("order.xml")//item
group by $g:= $item/@num > 500
return <group prodnumrange="{if ($g) then 'high' else 'low'}"
                 count="{count($item)}"/>

<group prodnumrange="high" count="5"/>
<group prodnumrange="low" count="1"/>

You could even use the query shown in Example 7-14 to group the items on ranges of product numbers.

xquery version "3.0";
for $item in doc("order.xml")//item
group by $g:= $item/@num - ($item/@num mod 100)
return <group prodnumrange="{$g}-{$g+99}" count="{count($item)}"/>

<group prodnumrange="400-499" count="1"/>
<group prodnumrange="500-599" count="3"/>
<group prodnumrange="700-799" count="2"/>