When duplicate delete oldest date and if equal delete either?

select * from tmp_autorecharge_work a where exists (select l.account_number from tmp_autorecharge_work l where a.account_number = l.account_number
  group by l.account_number having count(*) > 1) and a.last_update = (select min(b.last_update) from tmp_autorecharge_work b where b.account_number = a.account_number)

The above logic does list the older record, problem is in some cases there are two or more older records with the same date.

Basically I only want to leave the most recent row in the table, if there are two rows with the same last_update, remove the first in. The above is a select I planned to turn into a delete statement.

thanks for any help or information. Received on Fri Sep 14 2007 - 06:32:08 CDT