Finding Most Recent Order for a Particular Item

Question

I'm trying to write a SQL Query for DB2 Version 8 which retrieves the most recent order of a specific part for a list of users. The query receives a parameter which contains a list of customerId numbers and the partId number. For example,

Order Table

OrderID
PartID
CustomerID
OrderTime

I initially wanted to try:

Select * from Order
where
    OrderId = (

        Select orderId
        from Order
        where 
        partId = #requestedPartId# and customerId = #customerId#
        Order by orderTime desc
        fetch first 1 rows only
    );

The problem with the above query is that it only works for a single user and my query needs to include multiple users.

Does anyone have a suggestion about how I could expand the above query to work for multiple users? If I remove my "fetch first 1 rows only," then it will return all rows instead of the most recent. I also tried using Max(OrderTime), but I couldn't find a way to return the OrderId from the sub-select.

Thanks!

Note: DB2 Version 8 does not support the SQL "TOP" function.

Answer 1

Try the following one. I didn't test it. The idea is that you first find all orders for all your specified customers. These will be grouped and you find the biggest order time for each customer (combination of group by and max). This is the foo query, that identifies the records that you need. Than you join it with your order table to retrieve the necessary information for these orders.

select o.*
from order o inner join 
    (select customerId, max(orderTime)
    from order o
    where customerId in ( #customerIds#)
      and partId = #requestedPartId#
    group by customerId) foo
    on o.customerId = foo.customerId 
     and o.orderTime = foo.orderTime

EDIT: The above query gives you the most recent order for each customer you specified under the condition, that there is only one order per customer and orderTime. To get only one order it is slightly different. The following example assumes that the orderTime is unique, meaning there are no two orders at the same time in the database. This is generally be true if orderTime is recorded in milliseconds.

select o.*
from order o inner join 
    (select customerId, max(orderTime)
    from order o
    where customerId in ( #customerIds#)
      and partId = #requestedPartId#) foo
     on o.orderTime = foo.orderTime