order by clauses

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, working from left to right.

SELECT XMLQUERY(
  'declare namespace ipo="http://www.example.com/IPO";
  for $i in $po/ipo:purchaseOrder/items/item
  order by xs:decimal($i/USPrice) descending
  return fn:concat($i/productName, ":US$", $i/USPrice)'
  PASSING PORDER as "po")
FROM PURCHASEORDER

<ipo:purchaseOrder
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ipo="http://www.example.com/IPO"
   orderDate="2008-12-01">
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Joe Lee</name>
    <street>66 University Avenue</street>
    <city>Palo Alto</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <billTo xsi:type="ipo:USAddress">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="833-AA">
      <productName>Lapis necklace</productName>
      <quantity>1</quantity>
      <USPrice>99.95</USPrice>
      <ipo:comment>Want this for the holidays!</ipo:comment>
      <shipDate>2008-12-05</shipDate>
    </item>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</ipo:purchaseOrder>

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($i/USPrice) is 99.95. The expression is then evaluated for the next tuple, and the expression returns the value 178.99. Because the ordering specification indicates that items are sorted in descending order, the product with the price 99.95 sorts before the product with the price 178.99. This sorting process continues until all tuples are reordered. The return clause then executes once for each tuple in the reordered tuple stream.

Sapphire Bracelet:US$178.99 Lapis necklace:US$99.95

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

Explicit type conversion is also required when the dynamic type of the ordering key value is xs:untypedAtomic because the rules for comparing ordering keys dictate that untyped atomic data is treated as a string.

$ci/customerinfo[@Cid > "1000"]

SELECT XMLQUERY(
  'declare default element namespace "http://posample.org";
  for $custinfo in $ci/customerinfo
  where ($custinfo/@Cid > 1000)
  order by $custinfo/name ascending
  return $custinfo'
  PASSING XMLAGG(INFO) as "ci")
FROM CUSTOMER

SELECT XMLQUERY(
  'declare namespace ipo="http://www.example.com/IPO";
  for $i in $po/ipo:purchaseOrder/items/item
  order by xs:decimal($i/USPrice) descending
  return $i/productName'
  PASSING PORDER as "po")
FROM PURCHASEORDER