CODEWITHSUNDEEP
sqloracle-databasejoinoracle12c
z.rubi
z.rubi

Reputation: 327

How to JOIN 2 tables and keep only the most recent records

I have 2 tables I want to join. One table is a historical record of inventory that has a "last updated" date associated with each "piece" of inventory. The other table has the prices for each of those pieces. I want to join the tables so that I get the historical records with each of their prices. eg.

                  TABLE 1
Date         Item        Location           QTY 
06/01/2020   ABC         123                10
06/01/2020   DEF         234                12
06/02/2020   ABC         345                13
06/06/2020   ABC         123                10


                  TABLE 2
ITEM         Price
ABC          34.5
DEF          52.12

-----------------> result table ------------------>
Date         Item        Location           QTY       Price
06/01/2020   DEF          234               12        34.5
06/02/2020   ABC          345               13        52.12
06/06/2020   ABC          123               10        34.5

Where the result table filters so that it only keeps the most recent records. Eg. TABLE1 updates every minute to show new inventory levels. The item + location combination is "unique" in the sense that table1 is at the item/location level of granularity. However, there can be many of the same item/location combinations as the table updates and creates new entries (it is a historical table, so older entries with the same item + location combination remain in the table). Sometimes the date is different, sometimes the date is the same day.

The query I wrote to try to do this is:

SELECT DISTINCT
 TB1.DATE
,TB1.ITEM
,TB1.LOCATION
,TB1.QTY
,TB2.ITEM_COST

FROM
(
SCHEMA_1.TABLE1 AS TB1
JOIN  SCHEMA_1.TABLE2 AS TB2

ON TB1.ITEM = TB2.ITEM
JOIN (
      SELECT ITEM AS ITM,
          LOCATION AS LOC,
          MAX(DATE) AS MAXDATE
          FROM SCHEMA_1.TABLE1
          GROUP BY ITEM, LOCATION
      )TB3
  ON TB1.ITEM = TB3.ITM AND TB1.LOCATION= TB3.LOC AND TB1.DATE= TB3.MAXDATE
)

This query does execute but it gives me duplicates and definitely does not filter for the most recent records only. Not sure what I'm doing wrong here.

Upvotes: 0

Views: 295

Answers (3)

The AG
The AG

Reputation: 690

Columns names(dte= Date, LOC = Location) are changed but you can try this simple query to get the results:

Select dte dates, item, loc Locations, price, qty from 
(Select a.dte, a.item, a.loc,  b.price, a.qty,
max(a.dte) OVER (PARTITION BY a.item, a.loc) latest_dt 
from table1 a LEFT JOIN table2 b ON a.item = b.item) where dte = latest_dt
order by 1;

Output:

+-----------+------+-----------+-------+-----+
|   DATES   | ITEM | LOCATIONS | PRICE | QTY |
+-----------+------+-----------+-------+-----+
| 01-JUN-20 | DEF  | 234       | 52.12 | 12  |
+-----------+------+-----------+-------+-----+
| 02-JUN-20 | ABC  | 345       | 34.5  | 13  |
+-----------+------+-----------+-------+-----+
| 06-JUN-20 | ABC  | 123       | 34.5  | 10  |
+-----------+------+-----------+-------+-----+

You can also get Latest date as : max(a.dte) KEEP (DENSE_RANK FIRST order by dte desc) OVER (PARTITION BY a.item, a.loc )

Upvotes: 0

Turo
Turo

Reputation: 4914

Good old subselect should work, too. Assuming unqiqe Date per item, Location pair.

SELECT T1.* , T2.price 
FROM SCHEMA_1.TABLE1 AS TB1
JOIN SCHEMA_1.TABLE2 AS TB2 ON TB1.Item = TB2.Item
WHERE Date = (SELECT MAX(Date) FROM SCHEMA_1.TABLE1 AS TB3
              WHERE TB1.Item = TB3.Item
              AND   TB1.Location = TB3.Location)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would suggest:

SELECT t1.*, t2.ITEM_PRICE
FROM SCHEMA_1.TABLE1 t1 JOIN
     (SELECT t2.ITEM, t2.LOCATION,
             MAX(t2.ITEM_PRICE) KEEP (DENSE_RANK FIRST ORDER BY t2.DATE DESC) as ITEM_PRICE
      FROM SCHEMA_1.TABLE2 t2
      GROUP BY t2.ITEM, t2.LOCATION
     ) t2
     USING (ITEM, LOCATION);

Oracle has the convenient functionality to get the "first" or "last" value within a group. KEEP isn't the simplest syntax for this endeavor, but it does exactly what you want.

Upvotes: 0

Related Questions