CODEWITHSUNDEEP
xmlxpathxquery
Ben Whitely
Ben Whitely

Reputation: 59

XQuery: Returning the highest value after ordering

I'm still new to XQuery, but I've almost figured out this query, but it needs to just return the proteinID and the numberOfAuthors of the element with the most authors, however it's currently returning all of them instead of just the element with the highest number of authors.

My query is

for $i in doc("test.xml")/ProteinDatabase/ProteinEntry
let $author-count := count($i/reference/refinfo/authors)
let $proteinID := $i/@id
where $author-count = max($author-count)
order by $author-count descending
return ((<proteinID>{data($proteinID)}</proteinID>,
<numberOfAuthors>{data($author-count)}</numberOfAuthors>))

which returns:

<proteinID>QRHUA4</proteinID>
<numberOfAuthors>47</numberOfAuthors>
<proteinID>PLHU</proteinID>
<numberOfAuthors>29</numberOfAuthors>
<proteinID>LPHUB</proteinID>
<numberOfAuthors>29</numberOfAuthors>

and so on.

What I just want to return is the first proteinID and the highest which is 47, like below:

<proteinID>QRHUA4</proteinID>
<numberOfAuthors>47</numberOfAuthors>

I've tried a few things, like my where statement that's in there

where $author-count = max($author-count)

but it doesn't seem to have actually done anything. I've also tried some positional things and selecting only the first index, but it seems to only return the ID QRHUA4, but not the numberOfAuthors

Upvotes: 0

Views: 207

Answers (2)

Slkrasnodar
Slkrasnodar

Reputation: 824

for $i in //ProteinEntry[reference/refinfo/authors/count(*) = 
                         max(//ProteinEntry/reference/refinfo/authors/count(*))
                        ]
let $author-count := count($i/reference/refinfo/authors/author)
let $proteinID := $i/@id
return ((<proteinID>{$proteinID}</proteinID>,
<numberOfAuthors>{$author-count}</numberOfAuthors>))

fiddle: https://xqueryfiddle.liberty-development.net/jyH9Xv4/1

Upvotes: 1

Martin Honnen
Martin Honnen

Reputation: 167516

Well, you can simply select the first item in the result sequence using either

(for $i in doc("proteindb.xml")/ProteinDatabase/ProteinEntry
let $author-count := count($i/reference/refinfo/authors)
let $proteinID := $i/@id
where $author-count = max($author-count)
order by $author-count descending
return ((<proteinID>{data($proteinID)}</proteinID>,
<numberOfAuthors>{data($author-count)}</numberOfAuthors>)))[1]

or

head(for $i in doc("proteindb.xml")/ProteinDatabase/ProteinEntry
let $author-count := count($i/reference/refinfo/authors)
let $proteinID := $i/@id
where $author-count = max($author-count)
order by $author-count descending
return ((<proteinID>{data($proteinID)}</proteinID>,
<numberOfAuthors>{data($author-count)}</numberOfAuthors>)))

Upvotes: 1

Related Questions