• +49-(0)721-402485-12
Your experts for XML, XQuery and XML Databases

Group filtering and sorting

The check for existence of an item via which aggregation shall be made in order to eliminate empty grouping combinations essentially corresponds to the construct of the group filtering (in SQL realised by the HAVING clause). In general, filter conditions on groups are indicated in XQuery in the where clause of the appropriate FLWOR expression. If in the current example the output on the groups which represent at least three persons and whose average age is under 50 years is to be limited, this can be realised by adding the appropriate where clause:

<MedicalPersonnel>
{
for $o in fn:distinct-values(
for $i in fn:doc("...")//MedicalPersonnel/*/*
return fn:name($i)),
$g in fn:distinct-values(fn:doc("...")//Gender)
let $x := fn:doc("...")//
[fn:name(.) = $o and Gender=$g]/Age
order by $g, fn:avg($x) descending
return
(<OccupationalGroup>{ $o }<OccupationalGroup>,
<Gender>{ $g }</Gender>,
<Age>{ fn:avg($x) }</Age>)
}
</MedicalPersonnel>

In addition to the filtering on groups, an explicit sorting on all direct or derived variables for the formulation of a grouping query can be made. The above example shows a sorting initially done by the belonging to a gender and then by the average age of the respective person in descending order.

 

Source: "XQuery – Grundlagen und fortgeschrittene Methoden", dpunkt-Verlag, Heidelberg (2004)

<< backnext >>