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.