I need to find the last P.O.for parts purchased from Vendors.

I was trying to come up with a way to do this using a query I found that allowed me to find the max Creation date for a group of Quotes linked to an Opportunity:

SELECT 

t1.[quoteid]
,t1.[OpportunityId]
,t1.[Name]

FROM
[Quote] t1

WHERE 

t1.[CreatedOn] = (SELECT MAX(t2.[CreatedOn])
             FROM [Quote] t2

             WHERE t2.[OpportunityId] = t1.[OpportunityId])

In the case of Purchase Orders, though, I have a header table and a line item table. So, I need to include info from both:

SELECT

PURCHASE_ORDER.ORDER_DATE
,PURC_ORDER_LINE.PURC_ORDER_ID
,PURC_ORDER_LINE.PART_ID
,PURC_ORDER_LINE.UNIT_PRICE
,PURC_ORDER_LINE.USER_ORDER_QTY

FROM

PURCHASE_ORDER,
PURC_ORDER_LINE

WHERE

PURCHASE_ORDER.ID=
PURC_ORDER_LINE.PURC_ORDER_ID

If the ORDER_DATE from the header were available in the PURC_ORDER_LINE table I thought this could be done like so:

SELECT

PURC_ORDER_LINE.ORDER_DATE
,PURC_ORDER_LINE.PURC_ORDER_ID
,PURC_ORDER_LINE.PART_ID
,PURC_ORDER_LINE.UNIT_PRICE
,PURC_ORDER_LINE.USER_ORDER_QTY

FROM

PURC_ORDER_LINE T1

WHERE T1.ORDER_DATE=(SELECT MAX(T2.ORDER_DATE)
        FROM PURC_ORDER_LINE T2
        WHERE T2.PURC_ORDER_ID=T1.PURC_ORDER_ID)

But I'm not sure that's correct and, in any case, there are 2 things:

1. The ORDER_DATE is in the Header table, not in the line table
2. I need the last P.O. created for each of the Parts (PART_ID)

So:

PART_A and PART_B, as an example, may appear on several P.O.s

The result set would be (including the other fields from above):

I need a query that will give me such a result set.

CodePudding user response：

you can use window function:

select * from ( 
   select * , row_number() over (partition by PART_ID order by ORDER_DATE desc) rn
from tablename
) t where t.rn = 1

CodePudding user response：

You can use row-numbering for this. Just place the whole join inside a subquery (derived table), add a row-number, then filter on the outside.

SELECT *
FROM (
    SELECT
      pol.PART_ID,
      po.ORDER_DATE,
      pol.PURC_ORDER_ID,
      pol.UNIT_PRICE,
      pol.USER_ORDER_QTY,
      rn = ROW_NUMBER() OVER (PARTITION BY pol.PART_ID ORDER BY po.ORDER_DATE DESC)
    FROM PURCHASE_ORDER po
    JOIN PURC_ORDER_LINE pol ON po.ID = pol.PURC_ORDER_ID
) po
WHERE po.rn = 1;

Note the use of proper join syntax, as well as table aliases