Finding Most Recent Order for a Particular Item
- by visitor
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.
