Sql-server – Pick last Vendor Number (From Master table ) who sold Item number(From detail)

sql server

We have a master table for Receipts which has 

RCPSEQ, RCPNUMBER, PONUMBER, VENDORNUM, DATERCPT

AND Detail table which has:

RCPSEQ, PONUMBER, ITEM

I want to write a query to pick the Item number and the VendorNum who recently sold us that Item. Problem is grouping. with below query I am getting multiple PONUMBEr for same item and date.

SELECT       
    p1.PONUMBER, 
    P2.ITEMNO, 
    (P2.MxDTAR) AS DATEM
FROM            
    dbo.PORCPL P1
    join (select ITEMNO,   max(DTARRIVAL)as MxDTAR from PORCPL group by ITEMNO) as P2
        on P1.ITEMNO=p2.ITEMNO
order by 
    ITEMNO

Best Answer

You can use ROW_NUMBER() function to rank each item by it's date, being 1 the most recent one.

;WITH TimingRankingByItem AS
(
    SELECT
        P.*,
        TimeRanking = ROW_NUMBER() OVER (PARTITION BY P.ITEMNO ORDER BY P.DTARRIVAL DESC)
    FROM
        PORCPL AS P
)
SELECT
    T.*
FROM
    TimingRankingByItem AS T
WHERE
    T.TimeRanking = 1

Or using GROUP BY you should join by both the item number and the date, but if you have 2 max dates with the same item you will see both.

;WITH MaxDateByItem AS
(
    SELECT
        P.ITEMNO
        MaxDate = MAX(P.DTARRIVAL)
    FROM
        PORCPL AS P
    GROUP BY
        P.ITEMNO
)
SELECT
    P.*
FROM
    MaxDateByItem AS M
    INNER JOIN PORCPL AS P ON
        M.MaxDate = P.DTARRIVAL AND
        M.ITEMNO = P.ITEMNO
Related Question