XQuery

XQuery

(pwalmsley@datypic.com)

ISBN: 1491915103

2nd edition, , O'Reilly Media, Inc.

Chapter 7: Sorting and Grouping

Please note that the book contains many inline examples and informal tables that are not provided here.

Example 7-1. The order by clause
for $item in doc("order.xml")//item
order by $item/@num
return $item
Example 7-2. Using multiple ordering specifications
for $item in doc("order.xml")//item
order by $item/@dept, $item/@num
return $item
Example 7-3. Using an empty order declaration
Query
declare default order empty greatest;
for $item in doc("order.xml")//item
order by $item/@color
return $item
Results
<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"/>
Example 7-4. Inadvertent re-sorting in document order
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>
Example 7-5. FLWOR without inadvertent re-sorting
for $prod in doc("catalog.xml")//product
order by $prod/number
return <li>{string($prod/name)}</li>
Example 7-6. Using an order comparison
let $prods := doc("catalog.xml")//product
for $prod in $prods
where $prod << $prods[@dept = $prod/@dept][last()]
return $prod
Example 7-7. Using the unordered function
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}"/>
)
Example 7-8. An unordered expression
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}"/>
}
Example 7-9. Grouping by department
Query
for $d in distinct-values(doc("order.xml")//item/@dept)
let $items := doc("order.xml")//item[@dept = $d]
order by $d
return <department code="{$d}">{
         for $i in $items
         order by $i/@num
         return $i
       }</department>
Results
<department code="ACC">
  <item dept="ACC" num="443" quantity="2"/>
  <item dept="ACC" num="563" quantity="1"/>
</department>
<department code="MEN">
  <item dept="MEN" num="784" quantity="1" color="white"/>
  <item dept="MEN" num="784" quantity="1" color="gray"/>
</department>
<department code="WMN">
  <item dept="WMN" num="557" quantity="1" color="navy"/>
  <item dept="WMN" num="557" quantity="1" color="black"/>
</department>
Example 7-10. Grouping by department with group by clause
xquery version "3.0";
for $item in doc("order.xml")//item
let $d := $item/@dept
group by $d
order by $d
return <department code="{$d}">{
         for $i in $item
         order by $i/@num
         return $i}</department>
Example 7-11. Using multiple grouping specifications
Query
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)}"/>
Results
<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"/>
Example 7-12. Using nested FLWORs for multilevel grouping
Query
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>
Results
<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>
Example 7-13. Grouping high and low product numbers
Query
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)}"/>
Results
<group prodnumrange="high" count="5"/>
<group prodnumrange="low" count="1"/>
Example 7-14. Grouping on a range of values
Query
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)}"/>
Results
<group prodnumrange="400-499" count="1"/>
<group prodnumrange="500-599" count="3"/>
<group prodnumrange="700-799" count="2"/>
Example 7-15. Aggregation
Query
xquery version "3.0";
for $item in doc("order.xml")//item
group by $d := $item/@dept
order by $d
return 
  <department code="{$d}"
              numItems="{count($item)}"
              distinctItemNums="{count(distinct-values($item/@num))}"
              totQuant="{sum($item/@quantity)}"/>
Results
<department code="ACC" numItems="2" distinctItemNums="2" totQuant="3"/>
<department code="MEN" numItems="2" distinctItemNums="1" totQuant="2"/>
<department code="WMN" numItems="2" distinctItemNums="1" totQuant="2"/>
Example 7-16. Aggregation on multiple values
Query
xquery version "3.0";
for $item in doc("order.xml")//item
group by $d := $item/@dept, $n := $item/@num
order by $d, $n
return
    <group dept="{$d}" num="{$n}" numItems="{count($item)}"
           totQuant="{sum($item/@quantity)}"/>
Results
<group dept="ACC" num="443" numItems="1" totQuant="2"/>
<group dept="ACC" num="563" numItems="1" totQuant="1"/>
<group dept="MEN" num="784" numItems="2" totQuant="2"/>
<group dept="WMN" num="557" numItems="2" totQuant="2"/>
Example 7-17. Constraining and sorting on aggregated values
Query
xquery version "3.0";
for $item in doc("order.xml")//item
group by $d := $item/@dept, $n := $item/@num
where sum($item/@quantity) gt 1
order by count($item)
return
    <group dept="{$d}" num="{$n}" numItems="{count($item)}"
              totQuant="{sum($item/@quantity)}"/>
Results
<group dept="ACC" num="443" numItems="1" totQuant="2"/>
<group dept="WMN" num="557" numItems="2" totQuant="2"/>
<group dept="MEN" num="784" numItems="2" totQuant="2"/>
Datypic XQuery Services