1

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
0

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
0

Thanks Guys.. Got an Answer:

I grouped on Item for Detail table and then picked MAX(RCPSEQ) and later, in another view, linked the RCPSEQ with master table and selected the Vendor NUmber.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.