XQuery Most Recent Order for Each Customer

Question

I am trying to use Altova XMLSpy and XQuery 1.0 to return the most recent order for each customer.

In SQL the query is like this:

SELECT `Order ID`, `Customer ID`, `Employee ID`, `Order Date`
FROM Orders AS O1
WHERE `Order Date` =
  (SELECT MAX(`Order Date`)
   FROM Orders AS O2
   WHERE O2.[Customer ID] = O1.[Customer ID]);

Which returns 16 rows but I cannot get anything similar to work in XQuery.

I have tried multiple variations of code and I think the closest I have got is this:

<result>
{
    for $cust in distinct-values(doc("Orders.xml")//Orders/Customer_x0020_ID)
    return
    <Customer>
    {
        for $order in doc("Orders.xml")//Orders
        where $cust = $order/Customer_x0020_ID
        return max(xs:string($order/Order_x0020_Date))

    }
    </Customer>

}
</result>

Apologies for the terrible tag names the XML has been exported from MS Access.

Please help! Thanks in advance.

<Orders>
  <Order_x0020_ID>30</Order_x0020_ID>
  <Employee_x0020_ID>9</Employee_x0020_ID>
  <Customer_x0020_ID>27</Customer_x0020_ID>
  <Order_x0020_Date>2006-01-15T00:00:00</Order_x0020_Date>
</Orders>

Edit: After trying joemfb's solution I receive all orders for each customer when I need only the most recent (or max date):

<Customer>
    <Order_x0020_ID>57</Order_x0020_ID>
    <Customer_x0020_ID>27</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-22T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>30</Order_x0020_ID>
    <Customer_x0020_ID>27</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-01-15T00:00:00</Order_x0020_Date>
</Customer>
<Customer>
    <Order_x0020_ID>80</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>2</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-25T17:03:55</Order_x0020_Date>
    <Order_x0020_ID>58</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>3</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-22T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>61</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-07T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>34</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-02-06T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>31</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>3</Employee_x0020_ID>
    <Order_x0020_Date>2006-01-20T00:00:00</Order_x0020_Date>
</Customer>

Please, always give example data to work on. Everything else just leads to a bunch of follow-up question because of misinterpretation of the data. — Jens Erat, Mar 06 '14 at 22:01
What concrete database are you using? They all speak "SQL" as their query language - but things like XML support varies quite a bit from vendor to vendor. So please update your tags with the concrete database you're using - whether that's MySQL, Postgres, Sybase, IBM DB2, Oracle, SQL Server, Interbase - or whatever else it might be you're using! — marc_s, Mar 06 '14 at 22:02
For the SQL I was using MS Access but the XQuery is not using a database, the query is being run in Altova XMLSpy from a standalone XML document (of which I have now provided a snippet) — dinkydani, Mar 06 '14 at 22:13
Surely your file contains more than one order? If so, how are multiple orders represented? — Michael Kay, Mar 07 '14 at 08:48
@MichaelKay yes they do, the XML is badly named so the snippet provided is for one order and just repeated for multiple ones. These are contained in a node. — dinkydani, Mar 07 '14 at 16:24

joemfb · Accepted Answer · 2014-03-07T22:10:52.503

2

Update: I've modified the query to return all the elements for only the most recent order. This query is a little awkward, since your source XML doesn't group elements by order.

<result>
{
  for $cust in distinct-values(doc("Orders.xml")//Orders/Customer_x0020_ID)
  return
    <Customer>
    {
      let $date :=
      (
        for $cid in doc("Orders.xml")//Orders/Customer_x0020_ID[. eq $cust]
        let $date := $cid/following-sibling::Order_x0020_Date[1]
        order by xs:dateTime($date) descending
        return $date
      )[1]
      return
      (
        $date/preceding-sibling::Order_x0020_ID[1],
        $date/preceding-sibling::Customer_x0020_ID[1],
        $date/preceding-sibling::Employee_x0020_ID[1],
        $date
      )
    }
    </Customer>
}
</result>

edited Mar 07 '14 at 22:10

answered Mar 06 '14 at 22:32

joemfb

3,056
20
19

Except that the original SQL query returned `Order ID`, `Customer ID`, `Employee ID`, `Order Date`, whereas this only returns the date. – Michael Kay Mar 07 '14 at 08:47
Alas it works but returns all the orders for each customer (will update my question with the data). I need only the most recent order per customer, which is what i was having trouble doing :( – dinkydani Mar 07 '14 at 19:36
I've spent some time trying to work out how this query works but I am struggling, especially with the line `let $date := $cid/following-sibling::Order_x0020_Date[1]` and the preceding sibling bits also. Was hoping I could get an explanation? Thanks :) – dinkydani Mar 12 '14 at 14:58

score 1 · Answer 2 · answered Mar 07 '14 at 08:58

An XQuery 3.0 solution.

A higher-order function like saxon:highest() can be useful here: see

http://www.saxonica.com/documentation/#!functions/saxon/highest

With such a function, the code becomes something like the following (I'm assuming that the "Orders" element in the sample data represents one order, and is repeated):

for $o in //Orders
group by $o/Customer_ID
return saxon:highest($o, function($order){xs:date($order/Order_Date)})

If you're not using Saxon you can write the saxon:highest() function yourself, like this:

declare function saxon:highest($s as item()*, $f as function(item()*) as xs:anyAtomicValue) as item()?{
  if (count($s) lt 2)
  then head($s)
  else (
      let $h := saxon:highest(tail($s), $f)
      return if ($f(head($s)) gt $f($h))
             then head($s)
             else $h
}

XQuery Most Recent Order for Each Customer

2 Answers2