Chapter 7: Sorting and grouping

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

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

Example 7-4. Inadvertent resorting 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 resorting

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,
    $product in doc("catalog.xml")//product
where $item/@num = $product/number
return <item number="{$item/@num}"
             name="{$product/name}"
             quantity="{$item/@quantity}"/>
)

Example 7-8. An unordered expression

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

Example 7-9. Grouping by department

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>

Example 7-10. Aggregation

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}"
                   numItems="{count($items)}"
                   distinctItemNums="{count(distinct-values($items/@num))}"
                   totQuant="{sum($items/@quantity)}"/>

Useful function: max-string (see also functx:max-string)

declare namespace functx = "http://www.functx.com";
declare function functx:max-string ($stringSeq as xs:string*) as xs:string?{
   max($stringSeq)
};
(: Example call :)
functx:max-string(doc("order.xml")//item/@dept)

Useful function: min-non-empty-string (see also functx:min-non-empty-string)

declare namespace functx = "http://www.functx.com";
declare function functx:min-non-empty-string
($stringSeq as xs:string*) as xs:string? {
   min($stringSeq[. != ''])
};
(: Example call :)
functx:min-non-empty-string(doc("order.xml")//item/@dept)

Useful function: avg-empty-is-zero (see also functx:avg-empty-is-zero)

declare namespace functx = "http://www.functx.com";
declare function functx:avg-empty-is-zero
 ($allNodes as node()*, $values as xs:anyAtomicType*) as xs:double {
   if (empty($allNodes))
   then 0
   else sum($values[. != ""]) div count($allNodes)
};
(: Example call :)
let $prods := doc("prices.xml")//prod
return (functx:avg-empty-is-zero($prods, $prods/discount))

Example 7-11. Aggregation on multiple values

let $allItems := doc("order.xml")//item
for $d in distinct-values($allItems/@dept)
for $n in distinct-values($allItems[@dept = $d]/@num)
let $items := $allItems[@dept = $d and @num = $n]
order by $d, $n
return <group dept="{$d}" num="{$n}"
              numItems="{count($items)}"
              totQuant="{sum($items/@quantity)}"/>

Example 7-12. Constraining and sorting on aggregated values

let $allItems := doc("order.xml")//item
for $d in distinct-values($allItems/@dept)
for $n in distinct-values($allItems/@num)
let $items := $allItems[@dept = $d and @num = $n]
where sum($items/@quantity) > 1
order by count($items)
return if (exists($items))
       then <group dept="{$d}" num="{$n}" numItems="{count($items)}"
                   totQuant="{sum($items/@quantity)}"/>
       else ()