How to keep duplicates chains in database, using 2 columns to search, but with chained records with same ids

0
votes

How to keep only the most recent chain?

ID   Name  Chain_id  Chain_n
ABC  Juan  123       1
ABC  Juan  123       2
ABC  Juan  123       3
ABC  Juan  456       1
ABC  Juan  456       2
ABC  Juan  456       3
ABC  Juan  789       1 Keep this
ABC  Juan  789       2 Keep this
DEF  Ana   234       1
DEF  Ana   234       2
DEF  Ana   567       1 Keep this
GHI  Jill  345       1
GHI  Jill  345       2
GHI  Jill  678       1 Keep this
GHI  Jill  678       2 Keep this
GHI  Jill  678       3 Keep this

Tried to use common querys for duplicates, but based on just two columns it removes all records except 1, and I need to keep all the chain

select * from t where (columns-to-be-compared) in (select columns-to-be-compared from t group by columns-to-be-compared having count(*) > 1 )

I need only to keep the last 2 records for ABC,789,1 and 2 for example, the rows marked on the table, chains with greater chain_id

1
sqloracle

1 Answers

0
votes

We used an additional column "Name"

Delete from temptable where rowid in ( select rwid from ( select rowid rwid, row_number() over (partition by ID, Name order by chain_id desc) rn from temptable ) t where rn > 1 );