How can I delete pseudo-duplicates?

Question

A bit of background: I have a staging table without constraints which gets data for a given day, then some subsequent code that merges that data into a table with a key on it. I can get rid of perfect duplicates easily using this handy piece of code:

delete from tbstage where rowid in (
     select rowid from tbstage
   minus
     select min(rowid) from tbstage group by pkeyCol1, pkeyCol2, otherCols
);

This is great for getting rid of perfect duplicates that come in on the same day, and which would otherwise cause the merge to mess up due to trying to insert an unstable set of rows. However, there's another case that I'm seeing increasingly often and can't figure out how to handle. Sometimes, there will be 2 similar rows, except some of otherCols will be null. I don't know how to get rid of those rows in a systematic way without accidentally deleting other important data. This is what the problem data looks like:

      pkeyCol1, pkeyCol2, otherCol1, otherCol2
row1: 123,      ABC,      (null),    hello 
row2: 123,      ABC,      important, hello

In this case, I'd want to get rid of row1 and keep row2. What makes this a bit tricky is that otherCol1 might legitimately be null, and so could otherCol2. If row1 just has nulls, and row2 has values for the same fields, then I want to get rid of row1.

Thanks very much in advance -- this has been driving me berserk for several days now!

Answer 1

I this so ... Records with null values will be last(second) in group. Or you can use rn>1 if you have more than two duplicated rows.

SELECT ROWID FROM(
select t.*
      ,rowid
      ,row_number() over(PARTITION BY pkeycol1, pkeycol2 
                             ORDER BY othercol1,othercol2) rn
  from tbstage t)
  WHERE rn=2