SQL: How to Identify and Remove Duplicate Records from a Table? Part 2
***How to identify Duplicate records?
• SQL: How to Identify and Remove Dupli...
***How to remove duplicate records from Table/DB permanently?
ROWID Tutorial -
• SQL: What is the Difference between R...
ROWID will be generated in Ascending order for combination of fields
Record inserted at the end will have maximum ROWID.
Method 1 : Using ROWID with Group By
Select ID, NAME, SALARY, ROWID From DupTest Order By ID, ROWID;
Select Distinct Id, NAME, SALARY From DupTest;
Select ID,NAME, SALARY, MAX(ROWID) From DupTest Group By ID,NAME, SALARY;
101 Rajan 12000 AAAwG+AKTAAAAlMAAD
102 Pritesh 72000 AAAwG+AKTAAAAlMAAG
103 Karan 33000 AAAwG+AKTAAAAlMAAA
104 Manu 1200000 AAAwG+AKTAAAAlMAAH
105 Akif 200000 AAAwG+AKTAAAAlMAAI
106 Ravi 999999 AAAwG+AKTAAAAlLAAF
/*Actual Query - Keep the records having maximum ROWID for combination or records and remove rest of the records*/
Delete From DupTest
Where ROWID NOT IN
(Select MAX(ROWID) From DupTest Group By ID);
/*Check the result now*/
Select ID,NAME,SALARY,ROWID From DupTest order by ID;
#GoLearningPoint
For Interview questions and other topics -
https://tipsfororacle.blogspot.com/
