TOP N Records with 2 tables

[TheIMan]

Hi, I've been having trouble getting this query to return the values
I need. I have two related tables that contain raw material purchase
information. Currently, the query is returning ALL the pruchases for
a particular item, but I want to limit it to return 10 most recent
purchases for a particular item. I tried using the SELECT TOP
statement, but I keep putting it in the wrong places and the query
returned only 10 records period. I want the MOST RECENT 10 for EACH
item.
Can anyone help? Thank you. Below is the query as it stands
returning
all the records: -- Ira

SELECT RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.TotalPrice

FROM RawMaterialsInvtbl LEFT JOIN RawMaterialPurchaseRecords ON
RawMaterialsInvtbl.RawMaterialID =
RawMaterialPurchaseRecords.RawMaterialID

GROUP BY RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,

RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.TotalPrice

ORDER BY RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName;

 

[Allen Browne]

Use a subquery in the WHERE clause to limit your query to the 10 most recent
orders per product.

This untested example assumes there is a primary key named
RawMaterialPurchaseRecordsID in the RawMaterialPurchaseRecords table:

SELECT RawMaterialPurchaseRecords.RawMaterialID,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.RawMaterialPurchaseRecordsID
WHERE RawMaterialPurchaseRecords.RawMaterialPurchaseRecordsID IN
(SELECT TOP 10 Dupe.RawMaterialPurchaseRecordsID
FROM RawMaterialPurchaseRecords AS Dupe
WHERE Dupe.RawMaterialID = RawMaterialPurchaseRecords.RawMaterialID
ORDER BY RawMaterialPurchaseRecords.OrderDate DESC,
RawMaterialPurchaseRecords.RawMaterialPurchaseRecordsID DESC)
ORDER BY RawMaterialPurchaseRecords.RawMaterialID
RawMaterialPurchaseRecords.OrderDate DESC
RawMaterialPurchaseRecords.RawMaterialPurchaseRecordsID DESC;

Once you get that working, you can create another query using this one as an
input 'table', to get the outer join with RawMaterialsInvtbl.

If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html#TopN