Get entire record for Max value (date) in one column

date, max, ms-access, transactions

Solution

How about:

SELECT 
   tran.PONumber, 
   tran.Item, 
   tran.Vendor, 
   tran.Price, 
   tran.DateOrdered
FROM tran
WHERE tran.DateOrdered = (
   SELECT Max(DateOrdered) 
   FROM tran t 
   WHERE t.item=tran.item)

Where tran is your table.

Problem

I am trying to query data for transactions. I want to get multiple columns for the latest dated transaction. PONumber, Vendor, Price for each item, last time purchased. For example: Data: ``` PONumber Item Vendor Price DateOrdered 1 ABC Wal-Mart 1.00 10/29/12 2 ABC BestBuy 1.25 10/20/12 3 XYZ Wal-Mart 2.00 10/30/12 4 XYZ HomeDepot 2.50 9/14/12 ``` Desired Result Set: ``` PONumber Item Vendor Price DateOrdered 1 ABC Wal-Mart 1.00 10/29/12 3 XYZ Wal-Mart 2.00 10/30/12 ``` Trying to use max function on DateOrdered, but when I include the vendor I get the last purchase for each vendor and item (too many rows). I need one record for each item. Any ideas on how to accomplish? Using MS Access 2007 with ODBC to oracle tables. Thanks in advance.

Original source

Related problems