title: How can I find the column value that was modified latest from audit table using "left self exclusion join"?
body: <p>I have the following table design (it's immutable, and awful):</p>
  
  <p>Table 1: "ids": id1, id2. </p>
  
  <p>Maps each id1 to 1 or more id2 values.</p>
  
  <p>Table 2 "audit": id2, timestamp, unique_id. </p>
  
  <p>Maps each id2 to audit trail of its changes. unique_id is guaranteed to be unique for each row in the table</p>
  
  <p>Goal: for each distinct value of id1; select ONE row; containing whichever single id2 value (among those mapped to id1 in table 1) was modified latest according to table 2.</p>
  
  <p>I have tried doing the "left self exclusion join" approach outlined in <a href="https://stackoverflow.com/questions/3068268/getting-highest-results-in-a-join">this SO question</a> but couldn't quite figure out how to get it working. My query looked like:</p>
  
  <pre><code>select i1.id1, a2.id2
  from   ids i1
  right join audit a1 on i1.id2=a1.id2
  left join ids i2 on i1.id1=i2.id1
  left outer join audit a2 on i2.id2=a2.id2
  where a1.timestamp &lt; a2.timestamp
  and a1.unique_id!=a2.unique_id
  -- and a1.id2 is null
  </code></pre>
  
  <p>The problem is, this returns zero rows, because in the query (excluding last commented out line) <strong>none of the rows have zero id2</strong>, so I am guessing i got my outer join wrong.</p>
  
  <h1>Where am I going wrong here?</h1>
  
  <p>Example DB fiddle is here: <a href="http://sqlfiddle.com/#!6/f5c45/4" rel="nofollow noreferrer">http://sqlfiddle.com/#!6/f5c45/4</a></p>
  
  <pre><code>CREATE TABLE ids (id1 int, id2 int)
  CREATE TABLE audit (id2 int, timestamp int, unique_id int)
  
  insert into ids values (1,11)
  insert into ids values (1,12)
  insert into ids  values (2,23)
  insert into audit values (11,101,10000)
  insert into audit values (11,104,10001)
  insert into audit values (12,102,10002)
  insert into audit values (12,103,10003)
  insert into audit values (23,101,10004)
  </code></pre>
  
  <p>Expected result set from this data:</p>
  
  <pre><code>id1   id2   explanation
  1     11    id2=11 last modified at 104, id2=12 at 103.
  2     23    Only 1 row. That case tripped my query too.
  </code></pre>
  
  <p>Clarification: I'm aware that this problem can be solved via a correllated subquery without using left self exclusion join. I'm NOT interested in how to do that, I'm interested in what is wrong with my left self exclusion join query.</p>