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

jobs 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.

Given the column names my first thought is that this is an accounting application and you should never delete records.

Reconsider your approach. Add an additional column and updated it to flag the most recent record.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org