Re: When duplicate delete oldest date and if equal delete either?

On 14 Sep., 13:32, jobs <j..._at_webdos.com> wrote:
> 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.

If I understand correctly, you want to delete all records that are not the most recent entries for an account number? Then it would look thus:

delete from tmp_autorecharge_work
where (account_number, last_update) not in   (select account_number, max(last_update) from tmp_autorecharge_work group by account_number) Received on Fri Sep 14 2007 - 07:22:42 CDT