How to find Last Record with multiple results

  • dzirkelb

    SSC-Addicted

    Points: 446

    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

     

  • pietlinden

    SSC Guru

    Points: 62778

    Use ROW_NUMBER() with PARTITION BY?

    How about some sample data and expected results?

  • dzirkelb

    SSC-Addicted

    Points: 446

    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.

  • pietlinden

    SSC Guru

    Points: 62778

    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;
  • dzirkelb

    SSC-Addicted

    Points: 446

    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