Good morning,
This is a follow-up to SELECT most recent in Oracle SQL Query
I am hoping to take my Oracle skills to the next level after learning a lot from this site.
I work for a small construction company and thus, we buy a lot of smaller parts/materials from our vendors. Sometimes, in the same calendar year, we may switch who we buy the SAME part from. I want to only grab the most recent VENDOR for each individual PART NUMBER. Here is an example of what I mean: The code for my starting query:
WITH
PartNums AS -- Grabs me all of the stuff we "bought", and its vendor, in the construction division since Jan 1 2018
(
SELECT
PO_ITEM AS "PART_NUM",
VEND_NUM,
VEND_NM,
PODiv AS "DIVISION_CD"
FROM
INNER JOIN
(
SELECT MAX(PODate) OVER(PARTITION BY PO_Number, VEND_NUM))
FROM tblPurchases
WHERE PODate > '01-Jan-2017'
) tblTemp INNER JOIN tblPurchases ON tblPurchases.VEND_NUM = tblTemp.VEND_NUM
INNER JOIN tblVendors ON tblPurchases.VEND_NUM = tblVendors.VEND_NUM
WHERE
PODate > '01-Jan-2017'
AND
PODiv = 'C'
),
Defects AS -- Grabs me the listed defects against their stuff
(
SELECT
PartNums.*,
DEFECT_NUM,
DEFECT_CAT
FROM
PartNums
INNER JOIN tblDefects ON PartNums.PART_NUM = tblDefects.DEFECTIVE_PART_NUM
WHERE
DEFECT_DATE > '01-Jan-2017'
),
Names AS
(
SELECT
Defects.*,
PART_NM
FROM
Defects
INNER JOIN tblParts ON Defects.PART_NUM = tblParts.PART_NUM
)
SELECT
VEND_NUM,
VEND_NM,
PART_NUM,
PART_NM,
DEFECT_NUM,
DEFECT_CAT,
DIVISION_CD
FROM Names
This produces the following results:
| Vendor Number | Vendor Name | Part Number | Part Name | Defect Number | Defect Category | Division | Purchase Order Date |
|---------------|------------------------------|-------------|----------------|---------------|-----------------|----------|---------------------|
| 200123 | Push-Button LLC | 54211EW | Faceplate | PROB333211 | WRPT | C | 11-Jan-2017 |
| 200587 | Entirely Concrete | 69474TR | 2in Screw | PROB587412 | WRPT | C | 03-Mar-2017 |
| 200444 | Maaco | 77489GF | Hammer NR | PROB369854 | WRPT | C | 08-Aug-2017 |
| 200100 | Fleischman Contractors | 21110LW | Service | PROB215007 | OPYM | C | 01-Jun-2017 |
| 200664 | Advanced Tool Repair LLC | 47219UZ | Service | PROB9874579 | UPYM | C | 14-Jan-2018 |
| 200999 | AllTech Electronic Equipment | 36654DD | Plastic Casing | PROB326598 | NA | C | 16-Jan-2018 |
| 200321 | ZyotoCard Electronics | 74200ZN | Service | PROB012547 | MISCT | C | 19-Apr-2017 |
| 200331 | Black&Decker | 41122UT | .11mm Drillbit | PROB147741 | BRKN | C | 03-Aug-2017 |
| 200333 | Sears | 41122UT | .11mm Drillbit | PROB147741 | BRKN | C | 11-Mar-2017 |
As you can see, there are 2 vendors for Part Number 41122UT. For this part number, I only want Black & Decker (whose PO Date is 5 months newer than Sears).
I would like for the data to look like this:
| Vendor Number | Vendor Name | Part Number | Part Name | Defect Number | Defect Category | Division | Purchase Order Date |
|---------------|------------------------------|-------------|----------------|---------------|-----------------|----------|---------------------|
| 200123 | Push-Button LLC | 54211EW | Faceplate | PROB333211 | WRPT | C | 11-Jan-2017 |
| 200587 | Entirely Concrete | 69474TR | 2in Screw | PROB587412 | WRPT | C | 03-Mar-2017 |
| 200444 | Maaco | 77489GF | Hammer NR | PROB369854 | WRPT | C | 08-Aug-2017 |
| 200100 | Fleischman Contractors | 21110LW | Service | PROB215007 | OPYM | C | 01-Jun-2017 |
| 200664 | Advanced Tool Repair LLC | 47219UZ | Service | PROB9874579 | UPYM | C | 14-Jan-2018 |
| 200999 | AllTech Electronic Equipment | 36654DD | Plastic Casing | PROB326598 | NA | C | 16-Jan-2018 |
| 200321 | ZyotoCard Electronics | 74200ZN | Service | PROB012547 | MISCT | C | 19-Apr-2017 |
| 200331 | Black&Decker | 41122UT | .11mm Drillbit | PROB147741 | BRKN | C | 03-Aug-2017 |
I have found that using MAX() OVER (PARTITION BY) can be used to return the most recent, so I tried this query and it now runs, but it gives me the most recent date, for each vendor, for each part. Not just for each part. I need the MOST RECENT VENDOR INFORMATION (found on the Purchase Order, so ultimately need the most recent Purchase Order) for every PART. Could anyone advise?
WITH
PartNums AS -- Grabs me all of the stuff we "bought", and its vendor, in the construction division since Jan 1 2018
(
SELECT
PO_ITEM AS "PART_NUM",
VEND_NUM,
VEND_NM,
PODiv AS "DIVISION_CD"
FROM
INNER JOIN
(
SELECT PO_NUMBER, VEND_NUM, MAX(PODate) OVER(PARTITION BY PO_NUMBER, VEND_NUM))
FROM tblPurchases
WHERE PODate > '01-Jan-2017'
) tblTemp INNER JOIN tblPurchases ON tblPurchases.VEND_NUM = tblTemp.VEND_NUM
INNER JOIN tblVendors ON tblPurchases.VEND_NUM = tblVendors.VEND_NUM
WHERE
PODate > '01-Jan-2017'
AND
PODiv = 'C'
),
Defects AS -- Grabs me the listed defects against their stuff
(
SELECT
PartNums.*,
DEFECT_NUM,
DEFECT_CAT
FROM
PartNums
INNER JOIN tblDefects ON PartNums.PART_NUM = tblDefects.DEFECTIVE_PART_NUM
WHERE
DEFECT_DATE > '01-Jan-2017'
),
Names AS
(
SELECT
Defects.*,
PART_NM
FROM
Defects
INNER JOIN tblParts ON Defects.PART_NUM = tblParts.PART_NUM
)
SELECT
VEND_NUM,
VEND_NM,
PART_NUM,
PART_NM,
DEFECT_NUM,
DEFECT_CAT,
DIVISION_CD
FROM Names
Thank you very much for your time and help. Sorry if this creates any ambiguity.
Instead of using MAX, use DENSE_RANK, RANK or ROW_NUMBER and partition it by PO_NUMBER, VEND_NUM, order it by PO_DATE DESC, and filter out the records that returns value greater than 1,
Your query could be similar like below, as you can see I used DENSE_RANK,
SELECT *
FROM (SELECT A.*, DENSE_RANK() OVER(PARTITION BY PO_NUMBER, VEND_NUM ORDER BY podate DESC) rank_value
FROM your_table)
WHERE rank_value = 1;