CODEWITHSUNDEEP
xmlgroup-byxquerygrouping
Anvar
Anvar

Reputation: 434

XQuery grouping and finding max values

my XML document looks like the following:

<books>
    <book><author>A</author></book>
    <book><author>B</author></book>
    <book><author>C</author></book>
    <book><author>B</author></book>
    <book><author>C</author></book>
</books>

Now, the query should return the name of the authors with the most books. In this case B,C (since both have 2 books). The problem is, that it has to be only one query, so finding the maximum (2) and then searching for authors with 2 books is not an acceptable solution in this case.

Upvotes: 1

Views: 2717

Answers (1)

Jens Erat
Jens Erat

Reputation: 38682

Group by the authors and store the immediate result together with the count of their publications. Then filter to return only the authors with maximal publication count.

let $authors :=
  for $book in //book
  let $author := $book/author
  group by $author
  return <author count="{ count($book) }">{ $author }</author>
return $authors[@count = max($authors/@count)]/data()

Upvotes: 3

Related Questions