Duplicate rows in the table

Duplicate rows in the table:-
--------------------------------

The following query can be used to get the duplicate records from table.

SELECT * FROM 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);

To eliminate/delete the duplicate rows from the table, you can use the following query.

DELETE 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);