title: How to remove duplicates from an Oracle result set based on multiple tables
body: <p>I have 2 tables: a main APPLICATION table that holds the core data, and a STATUSTRACKING table that reflects the status changes of the core data in the APPLICATION table.</p>
  
  <p>There is a 1:N relationship between the APPLICATION table and the STATUSTRACKING table. Examples follow:</p>
  
  <pre><code>-- APPLICATION
  SELECT A.ID, A.DECISIONON, A.APPLICATIONSTATUS_ID AS CURRENTSTATUS
  FROM APPLICATION A
  WHERE A.ID=1099;
  
  Results:
  ID      DECISIONON  CURRENTSTATUS
  1099    5/05/2009   5
  
  -- STATUS TRACKING
  SELECT ST.ID, ST.APPLICATION_ID AS APP_ID, ST.APPLICATIONSTATUS_ID AS STATUS, ST.CREATEDATE 
  FROM STATUSTRACKING ST
  WHERE ST.APPLICATION_ID=1099
  ORDER BY ST.CREATEDATE DESC;
  
  Results:
  ID      APP_ID  STATUS  CREATEDATE
  44466   1099    5       5/05/2009
  44458   1099    7       5/05/2009
  10826   1099    8       21/07/2008
  9770    1099    7       9/07/2008
  4410    1099    3       9/05/2008
  3814    1099    2       2/05/2008
  3803    1099    1       2/05/2008
  </code></pre>
  
  <p>Problem:
  I need to select the STATUSTRACKING record with the MOST RECENT DATE for a particular status. This is my attempt:</p>
  
  <pre><code>SELECT A.ID AS APP_ID, A.APPLICATIONSTATUS_ID AS CURR_ST, Z.ID AS ST_ID, Z.CREATEDATE, Z.APPLICATIONSTATUS_ID AS OLD_ST
  FROM APPLICATION A, STATUSTRACKING Z
  WHERE A.APPLICATIONSTATUS_ID in (5,6)
      AND A.ID IN (337,1099,1404,9441)
      AND Z.APPLICATIONSTATUS_ID = 7
      AND Z.APPLICATION_ID=A.ID
  ORDER BY A.ID ASC, Z.CREATEDATE DESC;
  
  Results:
  APP_ID  CURR_ST ST_ID       CREATEDATE  OLD_ST
  337     6       13978       17/08/2008  7
  1099    5       44458       5/05/2009   7
  1099    5       9770        9/07/2008   7
  1404    6       15550       28/08/2008  7
  9441    5       49271       3/06/2009   7
  9441    5       46058       13/05/2009  7
  </code></pre>
  
  <p>The problem is the duplicated rows. I must only show the row with the MOST RECENT CreateDate. In the case of App_ID 1099, it would be this record:</p>
  
  <pre><code>1099    5       44458       5/05/2009   7
  </code></pre>
  
  <p>I obviously don't want to exclude the rows that are not duplicated.</p>
  
  <p>I thought I was on the right track with this statement which gives the row I'm looking for:</p>
  
  <pre><code>SELECT A.ID, A.APPLICATION_ID, A.APPLICATIONSTATUS_ID, A.CREATEDATE
  FROM    (SELECT * 
              FROM STATUSTRACKING 
              WHERE APPLICATIONSTATUS_ID=7 
                  AND APPLICATION_ID=1099 
              ORDER BY CREATEDATE DESC) A
  WHERE ROWNUM = 1;
  </code></pre>
  
  <p>... but I can't seem to get it to work with my main select statement.</p>
  
  <p>The result set I want should look like this:</p>
  
  <pre><code>APP_ID  CURR_ST ST_ID       CREATEDATE  OLD_ST
  337     6       13978       17/08/2008  7
  1099    5       44458       5/05/2009   7
  1404    6       15550       28/08/2008  7
  9441    5       49271       3/06/2009   7
  etc. ...
  </code></pre>
  
  <p>I'm no Oracle/SQL expert, so any help would be appreciated.</p>