title: How to JOIN 2 tables and keep only the most recent records
body: <p>I have 2 tables I want to join. One table is a historical record of inventory that has a &quot;last updated&quot; date associated with each &quot;piece&quot; 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.</p>
  <pre><code>                  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
  
  -----------------&gt; result table ------------------&gt;
  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
  </code></pre>
  <p>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 &quot;unique&quot; 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.</p>
  <p>The query I wrote to try to do this is:</p>
  <pre><code>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
  )
  </code></pre>
  <p>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.</p>