trouble with an SQL statement

I have a table with some bad data and I am trying to ID the "duplicates" in one sql statement to ease deleting the data.

The primary index has rogue values, but we know there are duplicates by common
values in a non index field (evt_desc).

Which is the original and which are the duplicates? The row posting last to a time stamp table is given to be the "original" - many timestamps per evt_event.

I have tried a corelated query, in-line view etc but can't make it work. Any ideas?

Heres the "bad table"

select evt_desc from evt_event
 where evt_desc in(
  select evt_desc
  from evt_event
  group by evt_desc
  having count(*) > 1
  )

returns all the duplicate rows for all cases - but only the description. For each description I need to ID the duplicate evt_code and entries as given by the timestamp table thus:

select max(mkt_timestamp)

   from mkt_snapshot
   where mkt_evt_code = evt_code.

Of course this doesn't work as we have more than one evt_code per evt_desc, also the max() ids the evt_evt we wish to keep, not those to delete.

The ones I want to identify and delete are *not max* timestamp value as opposed to min.

As I said I've tried a correlated query and inline views but get a failure on group by and othe types or errors.

Any help appreciated -plain sql if poss rather than pl/sql

thanks Received on Thu Apr 07 2005 - 17:02:54 CDT