title: Compare Source to Latest Record in Target
body: <p>I want to compare the records in the source table to the <strong>latest</strong> record (in each partition) in the target table</p>
  
  <p>I have already accomplished this by creating a temporary view with the latest record in each partition in the target</p>
  
  <p>Source:</p>
  
  <pre><code>id|name|salary|age  
  ------------------  
  1|John|2500|25  
  2|Mike|2500|30  
  </code></pre>
  
  <p>Target:</p>
  
  <pre><code>id|name|salary|age|sdate  
  ------------------------  
  1|John|1500|20|20190215  
  1|John|2000|22|20190318 
  </code></pre>
  
  <p>Get the latest record per partition in target and compare non-key columns to corresponding non-key columns in source:  </p>
  
  <pre><code>CREATE TEMPORARY VIEW latest_rec AS
  SELECT id, name, salary, age
  FROM ( SELECT id, name, salary, age,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY sdate DESC) AS RN
         FROM target_tab) tt
  WHERE tt.RN = 1
  </code></pre>
  
  <pre><code>...
  SELECT s.id, s.name, s.salary, s.age
  FROM src_tab s
  LEFT OUTER JOIN latest_rec t
  ON s.id = t.id
  AND MD5(s.name || s.salary || s.age) &lt;&gt; MD5(t.name || t.salary || t.age) 
  </code></pre>
  
  <p>Expected Output:</p>
  
  <pre><code>id|name|salary|age|sdate  
  1|John|1500|20|20190215  
  1|John|2000|22|20190318
  1|John|2500|25|20190525
  2|Mike|2500|30|20190525
  </code></pre>
  
  <p>I was wondering if there were a better and smarter way to achieve the same functionality. Please let me know if additional information is necessary.
  Thanks</p>