In Access, there is a LAST function, which simply allows you to do a group by, then select last, and you get the last record for each of your results. This does not exist in SQL.
I have a query I am running that will show me a full list of all Part ID's in a given year; however, I only want to know the LAST VendorID from these results.
For example, when I run the query just finding all the parts purchased, I get 4274 results. The moment I add in the VendorID to my query results, it jumps up to 4612 results, which is expected.
But, what I want is the Vendor ID for the LAST PO Date for each part ID. Here is my query which produces 4612 results. I need this modified to also include [PURCHASE ORDERS].[VendorID], but it needs to be the MAX [PURCHASE ORDERS].[PO DATE] for each PartID, which will result in 4274 results
SELECT NaftaEmailTemp.PartID, [PURCHASE ORDERS].VendorID
FROM [PURCHASE ORDERS] INNER JOIN PORECGS1 ON [PURCHASE ORDERS].[PO NUMBER] = PORECGS1.[PO Number] INNER JOIN NaftaEmailTemp ON PORECGS1.PartID = NaftaEmailTemp.PartID INNER JOIN [VENDOR MASTER] ON [PURCHASE ORDERS].VendorID = [VENDOR MASTER].VendorID
WHERE ([PURCHASE ORDERS].[PO DATE] > '4/1/2019') AND ([VENDOR MASTER].ExcludeNAFTAAutoRequest = 0) AND (PORECGS1.[Qty Recd] > 0) AND (PORECGS1.[PO Qty] = PORECGS1.[Qty Recd] + PORECGS1.[Unreceived Qty]) AND ([PURCHASE ORDERS].TYPE = 'P')
GROUP BY NaftaEmailTemp.PartID, [PURCHASE ORDERS].VendorID