Re: trouble with an SQL statement

FairPlay wrote:

> 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

One simple solution:
http://www.psoug.org
click on Morgan's Library
click on Constraints
scroll down to the "exceptions into" demo

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)