March 31, 2020 at 7:30 pm
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
March 31, 2020 at 7:39 pm
Use ROW_NUMBER() with PARTITION BY?
How about some sample data and expected results?
March 31, 2020 at 8:38 pm
I am not familiar with either of those functions.
Here would be some example data and what I'd like for the actual result:
VendorID: 101
PartID: 10
PO Date: 1/1/2020
VendorID: 201
PartID: 10
PO Date: 2/1/2020
I wany my query results to show VendorID 201, PartID: 10, and PO Date: 2/1/2020 only, I do not want to see the VendorID 101 at all in the results as it is not the last PO placed for the PartID of 10.
March 31, 2020 at 9:27 pm
Like this?
use tempdb;
go
CREATE TABLE SomeData (
VendorID INT NOT NULL,
PartID INT NOT NULL,
PODate DATE NOT NULL);
GO
INSERT INTO SomeData VALUES(101,10,'1/1/2020'),(201,10,'2/10/2020');
-- setup is done, now answer the question...
SELECT *
FROM (
SELECT PartID
, VendorID
, PODate
, ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY PODate DESC) AS PartRN
FROM SomeData
) x
WHERE x.PartRN = 1;
April 2, 2020 at 6:46 pm
That did the trick, thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply