Find latest ProductID record

[molly]

i have a table with a Purchase Order date and our RawID plus some other fields. In my query, i wanted to see the last purchase date for EACH rawId. For example, if i had multiple records for a rawID but with many PO dates, I would like ONLY the latest record for each rawID:

rawid PO date units
Green shirts 8/22/2006 124
Blue shirts 3/3/2006 521
Orange shirts 12/21/2006 12

thanks

 

[PHV]

SQL code:
SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT rawid, Max([PO date]) AS LastDate FROM yourTable GROUP BY rawid
) AS L ON A.rawid = L.rawid AND A.[PO date] = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886