Different ways to Delete Duplicate Records - Interview Questions Part 1

1) How to find duplicated records and write all possible queries to find duplicate records?

Method 1: Using Rowid

Ans: When someone ask you to get duplicate then you need to give preference write query using ROWID as its faster to retrieve records from data file.

SQL> select * from dept_1;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
6 rows selected


we have taken above table for this scenario and  here we could see dept no 10 and 20 are repeated so lets find out using query.



First lets understand about rowid here,

ROWID: rowid is pseudocolumn and it can be used as Data type same as integer, varchar2.

Oracle server will assign unique address for each record in each table to identify row in table.

we can see in above screenshot that 10,20 having different rowid it means we can understand that there are more than 1 record for deptno 10 and 20.

Below query is showing our required output and if you NOT IN in below query then you will get only duplicate records.

SQL> select * from dept_1 where rowid in (select min(rowid) from dept_1 group by deptno);
 
DEPTNO DNAME          LOC
------ -------------- -------------
    30 SALES                  CHICAGO
    20 RESEARCH         DALLAS
    40 OPERATIONS     BOSTON
    10 ACCOUNTING   NEW YORK


Method 2: Using Group by and having clause.

SQL> select deptno from dept_1    group by deptno having count(deptno) >1;
DEPTNO
------
    20
    10


Method 3: Using Count Analytical function and Inline view.

Here we are using count function and then finally with inline view we are getting output.

SQL> select * from (select  deptno, count(*)over(partition by deptno order by deptno) rn from dept_1)   where rn>1
 
DEPTNO         RN
------ ----------
    10          2
    10          2
    20          2
    20          2

Method 4: Using Row_number Analytical function and Inline view.

SQL> select * from
   (select deptno, row_number()over(partition by deptno order by deptno) r_number, rowid from dept_1)    where r_number <>1

  DEPTNO   R_NUMBER ROWID
------ ---------- ------------------------------------
    10          2 ABE8AUAF7AAAx4fAAA
    20          2 ABE8AUAF7AAAx4fAAB


Method 5: Using Dense_rank Analytical function and Inline view.

Note: in below query we are using "Order by rowid" to get sequential values.

 SQL> select * from
    (select deptno, dense_rank()over(partition by deptno order by rowid) r_number, rowid from dept_1)     where r_number <>1
 
DEPTNO   R_NUMBER ROWID
------ ---------- --------------------
    10          2 ABE8AUAF7AAAx4fAAA
    20          2 ABE8AUAF7AAAx4fAAB



Interview Questions Part2: Check out here ---> Interview Questions Part-2

Interview Questions Part3: Check out here ---> Interview Questions Part-3