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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy