I have been asked to build a query based on a table in our company database. The table holds all the transactional history of our inventory, including current inventory and inventory that has been sold. All records related to the inventory share a common ID number and each transaction has a number. What I need to do is build a query that shows only the current inventory in the database, but I am not sure how to filter out all the other records :-S
I have made a simplified version of what the table looks like in the attached document. Basically, I need to return any records where the latest transaction_id for an id is either 11 or 20 and co_pack = Y. So, from the example, I would want to return the records highlighted in yellow. I have tried a few things using the MAX function, but I can't get it to work. Please can someone have a look and possibly point me in the right direction?