XQuery: how to sort on a count variable

, ,
1

My “sortby” on a calculated variable (stores the count) is not working. And I’d like to learn why not.

Here is my XQuery:


{
let $doc := document(“Customers.xml”)
for $z in distinct-values($doc/CustomerList/customer/address/zip)
let $c := count($doc/CustomerList/customer[address/zip = $z])
return




sortby(count(document(“Customers.xml”)/CustomerList/customer/address/zip = ./address/zip))
}
{ $z/text() } { $c }


And here is my XML with a little bit of what I’m trying to do:

I’m displaying the number of customers for each zip code. Sort the result by the number of Customers in a descending order.

Here is the .XML file:
=========Start of XML==============
<?xml version="1.0"?>


Alexander

406
Washington
Seattle
WA
98104




Ali

132
Main
Los Angeles
CA
90007



===================================

2

Your sortby expression

sortby(count(document(“Customers.xml”)/CustomerList/customer/address/zip = ./address/zip))

reduces to

sortby(count(D/C/C/A/Z = ./Z/Z))

so the argument to count() is a boolean, and the count of a boolean is always 1 (a singleton value in XQuery is a sequence of length one).

It’s not clear to me what you actually want. Isn’t it just sortby $c?

Michael Kay

3

I see your point about the boolean now.
However, I did try sortby( $c ) as my first option, but I get this error



Yes you are right in that all I want is something that does this

sortby( $c )

but the error is preventing this type of syntax, so I’m wondering how to fix it. Do you have any suggestions?

4

Hi,
I am trying to do the same thing as cvillegas (sort by a count variable). For example, I am trying to sort my results by the number each customer appears in a certain city. Here is a sample of what I am trying to do:

for $city in distinct-values(
document(“customers.xml”)/customerData/customer/address/city)
let $i := document(“customers.xml”)/customerData/customer/address[city = $city]
return
{count($i)}
sortby (count($i))

However, it seems as if sortby doesn’t have access to $i (error message indicates $i is an unknown variable). I would like to sort my results based on count. Here is some sample data (I would like the “New York” result to appear at the top of the returned results since it has one customer versus “Los Angeles” which has two customers):

622543790 Philip Los Angeles 618546905 Micheal Los Angeles 612389150 Nitin New York

Any ideas on how I might accomplish this?

5

In Tamino, this is pretty simple:
for $city in distinct-values(
input()/customerData/customer/address/city)
let $i := input()/customerData/customer/address[city = $city]
return
{count($i)}
sortby (city)

Note that your query cannot be a Tamino query, because Tamino does not support the document() function

6

Hi,
Maybe I am in the wrong forum, but I am using Quip to run the original query I posted. Quip does support the document function as my query is working (just not the sortby on a count).

It looks to me like the example you gave sorts by city (although I'm not sure which element underneath city is uses to order this sort).  However, I wanted to sort by the count.  Perhaps your query is sorting by count, and I am just misunderstanding it.

Thanks

7

I have attached a picture of the query to make things more clear. (This query is just sorting by city, not the desired query, which would sort by count($i))
quip1.bmp (705 KB)

8

Here is an example I found on

The following query tests whether the most expensive book is also the book with the greatest number of authors and editors:

let $b1 := for $b in doc(“books.xml”)//book
order by count($b/author) + count($b/editor)
return $b

If I can make something like this work with Quip, I can make my above query work. This should be a fairly common/straightforward thing to do…
Does anyone know how to transform something like this into a Quip-like syntax?

Thanks in advance

9

“sort by” only applies to the “return” set, so $c and $i are out of scope for the sort by. In the example, you need to sort by the text node of the city element rather than the city element. With Tamino, one way to sort by the text content is:

declare namespace xs = “XML Schema”

for $city in distinct-values(input()/Document/City)
let $i := input()/Document/City[. = $city]
return {count($i)}
sort by (xs:integer(text()) descending)

(I assume you want counts sorted numerically rather than alphanumerically, so the xs:integer fuction should be used)

Depending on the flexibility of what you can use for a return document, it may be clearer to return name and count as separate elements or attributes:

declare namespace xs = “XML Schema”

for $city in distinct-values(input()/Document/City)
let $i := input()/Document/City[. = $city]
return {$city}{count($i)}
sort by (xs:integer(count) descending)

10

sorting by City means (for tamino) sorting by the contents of city, and this is count($i).

However, you are using Quip, which is out of date

11

Thanks everyone,
from your advice I was able to figure out how to do the sorting by count. It basically turned out to be sorting by count of the returned XML element name.

For example, something like this worked for me:

…
return
…
{count($i)}

sortby (int(string(CustomerNum)))

Thanks!