• Interview Question: How to Find Duplicate Records in Table?

1. How To Delete Duplicate Rows In Sql
2. Oracle Sql Find Duplicate Records

Answer: To Find Duplicate Records in table you can use following query:

select a.* from Employee a where rowid !=
(select max(rowid) from Employee b where a.Employee_num =b.Employee_num;

1.Command Used to fetch records:

List All Rows Containing Duplicates. In the previous step, our query returned a list of duplicates. Now, we want to return the entire record for each duplicate row. To accomplish this, we’ll need to select the entire table and join that to our duplicate rows. Our query looks like this. Script Name 12.2: Find duplicate SQL statements with PL/Scope; Description PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code. You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like: Where is a variable assigned. SQL delete from emp. Where rowid not in. (select max(rowid) from emp group.

Select * from Employee;

So we will start analysing above table.First we need to calculate the records or
fetch the records which are dupicate records.

How To Delete Duplicate Rows In Sql

We are again using concept of row_id here.So i am displaying row_ids of the
employees.

Oracle Sql Find Duplicate Records

select e.*,e.row_id from Employee e;

Here you will see or analyse that for the duplicate records the row_ids are different.So our logic is fetch the records where the row_id is maximum.But we need to take care of joining condition because we want data for specific group.So in our table we will use Employee_num as condition.

So to Fetch the Duplicate records from table following is the Query:

select a.* from Employee a where rowid !=
(select max(rowid) from Employee b where a.Employee_num =b.Employee_num;

It will fetch following results:

Using Simple delete statement you can remove the duplicate records from the table.

Use Following Query:

Delete from Employee a where rowid != (select max(rowid) from Employee b where a.Employee_num =b.Employee_num;

Query 2 : Suggested By Reader Sai Jagdish:

DELETE FROM EMPLOYEE WHERE ROWID IN(SELECT ROWID FROM (SELECT E.*, ROW_NUMBER() OVER (PARTITION BY EMPNO ORDER BY EMPNO) RANK, ROWID FROM EMPLOYEE E) WHERE RANK!=1);

Query 3 : Suggested By Reader Milan Dhore

select Employee_num,count(*) from Employee
group by Employee_num
having count(Employee_num) > 1;

On Request of Reader :

Query to Find duplicate records in Mysql :

Lets say User wants to find out duplicate Employee Number records from Employee table,

Hope the query explanation is helpful to you .Please post comment if any questions or queries if you have.These kind of questions are always asked in interviews.

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE