Removing duplicate records by using Oracle's ROWID

A table of records can be deduped by using ROWID column in Oracle. A ROWID is a unique location of a row in the...

database that never changes for the life of that row. By using ROWID, Oracle Optimizer would select the fastest path, which is a direct route to a single row of data. The following code selects the most current record for every duplicate pair of col1 and col2.

		
Select col1,col2,col3,col4 from TableName where
ROWID in (select max(ROWID) from TableName
group by col1,col2)

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.