title: How to select last occurrence of duplicating record in oracle
body: <p>I am having a problem with Oracle query where the basic goal is to get the last row of every re-occurring rows, but there's a complication that you'll understand from the data:</p>
  
  <p>Suppose I have a table that looks like this:</p>
  
  <pre><code>ID    | COL1 | COL2 | COL3 | UPDATED_DATE
  ------|------|------|------|-------------
  001   | a    | b    | c    | 14/05/2013
  002   | a    | b    | c    | 16/05/2013
  003   | a    | b    | c    | 12/05/2013
  </code></pre>
  
  <p>You should be able to guess that since columns 1 to 3 have the same values for all 3 rows they are re-occurring data. The problem is, I want to get the latest updated row, which means row #2.</p>
  
  <p>I have an existing query that works if the table is without ID column, but I still need that column, so if anybody could help me point out what I'm doing wrong, that'd be great.</p>
  
  <pre><code>select col1,
         col2,
         col3,
         max(updated_date)
    from tbl
   order by col1, col2, col3;
  </code></pre>
  
  <p>The above query returns me row #2, which is correct, but I still need the ID.</p>
  
  <p>Note: I know that I could have encapsulate the above query with another query that selects the ID column based on the 4 columns, but since I'm dealing with millions of records, the re-query will make the app very ineffective.</p>