SQL Complex Queries

Query to find Duplicate Records in Table? | Query to Delete Duplicate Records from table

Amit S October 29, 2017

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

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;

CLICK HERE TO GET 20 COMPLEX SQL QUESTIONS

1.Command Used to fetch records:

Select * from Employee;

Employee_num Employee_name Department
1 Rahul OBIEE
1 Rahul OBIEE
2 Rohit OBIEE


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

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

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

Employee_num Employee_name Department Row_ID
1 Rahul OBIEE 5001
1 Rahul 5002
2 Rohit OBIEE 5003

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:

Employee_num Employee_name Department Row_ID
1 Rahul OBIEE 5002

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

CLICK HERE TO GET 20 IMPORTANT INTERVIEW QUESTIONS FOR TECH MAHINDRA

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,

SELECT
    Employee_Num,
    COUNT(Employee_Num)
FROM
    Employees
GROUP BY Employee_Num
HAVING COUNT(Employee_Num) > 1;

CLICK HERE TO GET INFORMATION ABOUT DIFFERENT ORACLE ERRORS

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.

Home

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

13 Replies to “Query to find Duplicate Records in Table? | Query to Delete Duplicate Records from table”

  1. 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);

    The above query will also remove the duplicates

  2. I think this straight way to get it

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

    1. Hello Anu,

      As per your request i have provided the query to find duplicate Employee_Id from Employee table in Mysql :
      SELECT
      Employee_Num,
      COUNT(Employee_Num)
      FROM
      Employees
      GROUP BY Employee_Num
      HAVING COUNT(Employee_Num) > 1

      Regards and thanks,
      Amit S

  5. Let’s say that if the table has two columns as id(int) and col(any data type)
    Then query for getting the duplicate records

    select t1.col
    from table t1, table t2
    where t1.col = t2.col
    and t.id t2.id ;

    Can this be a possible solution?

  6. select a.* from Employee a where rowid !=
    (select max(rowid) from Employee b where a.Employee_num =b.Employee_num;
    this will only fetch correct results if the no. of duplicates is 2 but when more what to do ?

Comments are closed.