DELETE FROM __table__ WHERE ROWID IN ( SELECT MyKey FROM ( -- use the RANK() function to assign a sequential number to each set of records. SELECT MyKey, display_name, RANK() OVER (PARTITION BY display_name ORDER BY MyKey) AS SeqNumber FROM ( -- use the ROWID psuedo-column to get a unique id for each record. SELECT ROWID AS MyKey, display_name FROM __table__ WHERE (display_name) IN ( -- select the set of records that are duplicates. SELECT display_name FROM __table__ GROUP BY display_name HAVING COUNT(*) > 1 ) ) ) WHERE SeqNumber > 1 )
Monday, June 03, 2002
I've found a neat way to delete duplicates from my database tables using Oracle's RANK() function.
Sunday, June 02, 2002
I've downloaded the Solarmetric's JDO implementation and have started to use it as the basis for JDO experimentation. One of my biggest concerns is how it handles datasets in the tens of millions. I'll be creating test data to see in the coming weeks and posting the results.
Posted by David Medinets at 6/02/2002 03:53:00 PM