How To Delete Duplicate Records in SQL Oracle

Methods covered:

1. ROWID with GROUP BY
2. ROWID with Self Join
3. ROWID with ROW_NUMBER
4. ROWID with RANK
5. ROWID with DENSE_RANK

1. Create the sample table

Drop table if it already exists:

DROP TABLE emp_dup;

Create table:

CREATE TABLE emp_dup (
  empno  NUMBER,
  ename  VARCHAR2(50),
  job    VARCHAR2(50),
  sal    NUMBER,
  deptno NUMBER
);

Insert sample data:

INSERT INTO emp_dup VALUES (101, 'KING',  'PRESIDENT', 1000, 10);
INSERT INTO emp_dup VALUES (102, 'CLARK', 'MANAGER',    800, 20);
INSERT INTO emp_dup VALUES (103, 'FORD',  'MANAGER',    750, 10);
INSERT INTO emp_dup VALUES (102, 'CLARK', 'MANAGER',    800, 20);
INSERT INTO emp_dup VALUES (104, 'JAMES', 'MANAGER',    820, 30);
INSERT INTO emp_dup VALUES (105, 'WARD',  'SALESMAN',   500, 10);
INSERT INTO emp_dup VALUES (104, 'JAMES', 'MANAGER',    820, 30);
INSERT INTO emp_dup VALUES (106, 'FORD',  'SALESMAN',   400, 10);
INSERT INTO emp_dup VALUES (104, 'JAMES', 'MANAGER',    820, 30);
INSERT INTO emp_dup VALUES (107, 'SMITH', 'SALESMAN',   450, 10);

COMMIT;

2. Solution 1: Using ROWID with GROUP BY Clause

This method keeps one row for each duplicate group and deletes the rest.

Delete duplicates:

DELETE FROM emp_dup
WHERE rowid NOT IN (
  SELECT MAX(rowid)
  FROM emp_dup
  GROUP BY empno, ename, job, sal, deptno
);

Alternative version:

DELETE FROM emp_dup
WHERE rowid NOT IN (
  SELECT MIN(rowid)
  FROM emp_dup
  GROUP BY empno, ename, job, sal, deptno
);

3. Solution 2: Using ROWID with Self Join

In this method, the row with the greater ROWID is deleted while keeping the row with the smallest ROWID.

DELETE FROM emp_dup a
WHERE rowid > (
  SELECT MIN(rowid)
  FROM emp_dup b
  WHERE a.empno  = b.empno
    AND a.ename  = b.ename
    AND a.job    = b.job
    AND a.sal    = b.sal
    AND a.deptno = b.deptno
);

4. Solution 3: Using ROWID with ROW_NUMBER

This method assigns a unique number to each duplicate row within a partition and deletes rows where the rank is greater than 1.

DELETE FROM emp_dup
WHERE rowid IN (
  SELECT rowid
  FROM (
    SELECT rowid,
           ROW_NUMBER() OVER (
             PARTITION BY empno, ename, job, sal, deptno
             ORDER BY rowid
           ) AS r
    FROM emp_dup
  )
  WHERE r > 1
);

5. Solution 4: Using ROWID with RANK

This method is similar to ROW_NUMBER, but RANK can assign the same value when duplicate ordering values exist.

DELETE FROM emp_dup
WHERE rowid IN (
  SELECT rowid
  FROM (
    SELECT rowid,
           RANK() OVER (
             PARTITION BY empno, ename, job, sal, deptno
             ORDER BY rowid
           ) AS r
    FROM emp_dup
  )
  WHERE r > 1
);

6. Solution 5: Using ROWID with DENSE_RANK

DENSE_RANK also works for duplicate removal and keeps the first row in each partition.

DELETE FROM emp_dup
WHERE rowid IN (
  SELECT rowid
  FROM (
    SELECT rowid,
           DENSE_RANK() OVER (
             PARTITION BY empno, ename, job, sal, deptno
             ORDER BY rowid
           ) AS r
    FROM emp_dup
  )
  WHERE r > 1
);

Which method should you use?

ROW_NUMBER is usually the cleanest and most commonly used method for deleting duplicates. It is simple, readable, and easy to maintain.

ROWID with GROUP BY is also popular because it is straightforward and efficient.

Example Result

After running any of the above solutions, duplicate rows such as repeated CLARK or JAMES records are removed, leaving only one copy of each distinct row.

Key Point:
ROWID helps identify individual rows uniquely, while analytic functions like ROW_NUMBER, RANK, and DENSE_RANK make duplicate removal easier and more flexible.