title: Delete all duplicate records from Oracle table except oldest
body: <p>I have 2 tables, one parent TableA and one child TableB. TableB has 1 or more records with a parent record in TableA. I need to delete all records from TableB except the earliest date i.e. all duplicates in TableB. I don't think TableA needs to be involved in the statement but I'm including it just for reference.</p>
  
  <pre><code>TableA 
  _______ 
  SecID, SecName 
  1,     Sec1
  2,     Sec2
  3,     Sec3
  4,     Sec4
  
  TableB
  _________
  IncID, SecID, PayDate 
  16,    1,     11/03/2011
  17,    1,     11/04/2011
  18,    2,     10/01/2011
  19,    3,     01/06/2011
  20,    3,     01/09/2011
  21,    3,     01/12/2011
  22,    4,     10/06/2011
  </code></pre>
  
  <p>So in TableB above I need to delete records 17, 20, and 21 leaving one record for each SecID. So far I have below but for some reason it's including the earliest record which I want to keep:</p>
  
  <pre><code>delete from TableB where PayDate not in (
    select min(PayDate)from TableB
    having ( count(PayDate) &gt; 1 )
  )
  </code></pre>