How to find 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);
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);
Leave a Reply
Want to join the discussion?Feel free to contribute!