3

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
jesbrown4154
  • 33
  • 1
  • 4
  • Bill Karwin has an elegant RDBMS-agnostic approach to this very problem: [Fetch the row which has the Max value for a column](http://stackoverflow.com/a/123481/154439). You'd have to do some benchmarking with your actual data to see whether his approach would work better or worse than @Remou's. – mwolfe02 Oct 30 '12 at 16:06
  • @user714965 So it is. Apologies. – Fionnuala Oct 30 '12 at 17:52

1 Answers1

3

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152