Xquery Top Function

Question

How can I achieve something similar to the TOP function is SQL using Xquery? In other words, how can I select the top 5 elements of something with ties? This should be simple but I'm having trouble finding it with Google.

An example of some data I might want to format looks like this:

<?xml version="1.0"?>
<root>
    <value> 
        <a>first</a>
        <b>1</b>
    </value>
    <value> 
        <a>third</a>
        <b>3</b>
    </value>
    <value> 
        <a>second</a>
        <b>2</b>
    </value>
    <value> 
        <a>2nd</a>
        <b>2</b>
    </value>
</root>

I want to sort by b for all of the values and return a. To illustrate my problem, say I want to return the top two values with ties.

Thanks

Answer 1

For the provided source XML document:

<root>
  <value> 
    <a>first</a>
    <b>1</b>
  </value>
  <value> 
    <a>third</a>
    <b>3</b>
  </value>
  <value> 
    <a>second</a>
    <b>2</b>
  </value>
  <value> 
    <a>2nd</a>
    <b>2</b>
  </value>
</root>

To get the first two results "with ties" use:

let $vals := 
  for $k in distinct-values(/*/*/b/xs:integer(.)) 
    order by $k
    return $k
 return
  for $a in /*/value[index-of($vals,xs:integer(b)) le 2]/a
      order by $a/../b/xs:integer(.)
    return $a  

When this expression is evaluated, the wanted, correct result is produced:

<a>first</a>
<a>second</a>
<a>2nd</a>

Explanation:

1. We specify in $vals the sorted sequence of all distinct values of /*/*/b, used as integers. This is necessary, because the function distinct-values() is not guaranteed to produce its result sequence in any predefined order. Also, if we do not convert the values to xs:integer before sorting, they would be sorted as strings and this would generally produce incorrect results.

2. Then we select only those /*/value/a whose b-sibling's index in the sorted sequence of distinct integer b-values is less or equal to 2.

3. Finally, we need to sort the results by their b-sibling's integer values, because otherwise they will be selected in document order

Do note:

Only this solution at present produces correctly sorted results for any integer values of /*/*/b.

Answer 2

To filter a sequence to the first 5 items you use the fn:position() function:

$sequence[position() le 5]

Do note that when the sequence to filter is a node set resultion from an / step operation, the predicate works againts the last axis. So, maybe you would need to wrap that expression between parentesis.

But, to filter a "calculated sequence" (like sorting or tuples filter conditions), you need to use the full power of the FLWOR expression.

This XQuery:

(for $value in /root/value
 order by $value/b
 return $value/a)[position() le 2]

Output:

<a>first</a><a>second</a>

Note: This is a simple sort. The filter is the outer most expression because this allows lazy avaluation.

This XQuery:

for $key in (for $val in distinct-values(/root/value/b)
             order by xs:integer($val)
             return $val)[position() le 2]
return /root/value[b=$key]/a

Output:

<a>first</a><a>second</a><a>2nd</a>

Note: This order the keys first an then return all the result for the first two keys.

Edit: Added explicit integer casting.

Answer 3

You can use XPath on the Node with top limit as indexes

    <one>
  <two>a</two>
  <two>b</two>
  <two>c</two>
  <two>d</two>
  <two>e</two>
  <two>f</two>
  <two>g</two>
  <two>h</two>
   </one>

Then

$xml_data/one/two[ 1 to 5 ]

$xml_data/one/two[ some_number to fn:last() ]

Answer 4

With sample input

<one>
  <two>a</two>
  <two>b</two>
  <two>c</two>
  <two>d</two>
  <two>e</two>
  <two>f</two>
  <two>g</two>
  <two>h</two>
</one>

The following is one way to get the first five rows:

for $two at $index in /one/two
  where $index <= 5
  return $two

Answer 5

In the MySQL dialect it's not called TOP, but LIMIT. When you google for "limit xquery" you will find:

http://osdir.com/ml/text.xml.exist/2004-02/msg00214.html

and

http://osdir.com/ml/text.xml.exist/2004-08/msg00115.html