0

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

Part Order Date P.O. #
PART_A 2020-08-17 PO12345
PART_A 2020-11-21 PO23456
PART_A 2021-07-08 PO29986
PART_B 2019-11-30 PO00861
PART_B 2021-08-30 PO30001

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

ORDER_DATE PURC_ORDER_ID PART_ID UNIT_PRICE ORDER_QTY
2021-07-08 PO29986 PART_A 321.00 12
2021-08-30 PO30001 PART_B 426.30 8

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

Bill Wiest
  • 3
  • 2
  • `FROM PURCHASE_ORDER, PURC_ORDER_LINE` Nope - don't use [old-style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – SMor Oct 12 '21 at 16:04
  • Is your intention to return the last order record by part with the UNIT_PRICE and ORDER_QTY aggregated for all other records of the the same part, if so, is UNIT_PRICE an AVG() or some variation of ORDER_QTY and UNIT_PRICE? – Ross Bush Oct 12 '21 at 16:18
  • No aggregation. This is for a costing report. We need to know what the last unit price paid was for each part in the system. If a part shows up on a hundred POs, I am only interested in what the unit price was for the last entered PO for that part (same for each part in the system). – Bill Wiest Oct 12 '21 at 16:50
  • Define "last"! It seems to be based on PURCHASE_ORDER.ORDER_DATE, PURCHASE_ORDER.ID - both in descending order. But a purchase order can have multiple details lines with the same part, correct? Will the related information be the same **always** (e.g., price)? Don't assume - go check. It might be unlikely but the real world is a messy place. And I also note an important (and often incorrect) assumption - that "order date" is the same as "creation date". Tread carefully. – SMor Oct 12 '21 at 17:21
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Oct 12 '21 at 18:01
  • @Charlieface, I don't see how that suggestion would work. Two tables need to be combined to get all the required data, but this solution doesn't appear to be usable for that requirement. – Bill Wiest Oct 12 '21 at 18:18
  • Just do a big join inside a derived subquery, add a row-number and then filter on the outside – Charlieface Oct 12 '21 at 18:20
  • @SMor, Yes, Last means the last entered Purchase Order for each given part, so, based on ORDER_DATE for sure. I don't know that ORDER_DATE needs to be in descending order, but for the ID, it definitely does not. The Purchase Order Id in the result is just for information (so you could go look up detail if you wanted). Yes, a PO could have the part on it more than once -- this does happen on occasion. The price can be different for each part (they don't all have the same price). ORDER_DATE is not necessarily the same as CREATE_DATE, but this doesn't matter for my purposes. I can use either one. – Bill Wiest Oct 12 '21 at 18:25
  • @Charlieface, could you provide an example of that (big join etc) -- sorry, I'm a true newbie at this stuff. For ex., I don't know what you mean by "derived subquery". – Bill Wiest Oct 12 '21 at 18:29

2 Answers2

0

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
eshirvana
  • 23,227
  • 3
  • 22
  • 38
0

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43