The following SELECT statement will find and display all duplicate rows in a table, except the row with the maximum ROWID. The example uses the dept table:
SELECT * FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname — Make sure all columns are compared
AND a.loc = b.loc);
The following statement will delete all duplicate rows in a table, except the row with the maximum ROWID:
DELETE FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname — Make sure all columns are compared
AND a.loc = b.loc);
Alternatively:
DELETE FROM dept a
WHERE 1 < (SELECT COUNT(deptno)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname — Make sure all columns are compared
AND a.loc = b.loc);
EXPLANATION
===========
Using the pseudocolumn ROWID is the fastest way to access a row. ROWID represents a unique storage identification number for a single row in a table (Note: Two rows on different tables but stored in the same cluster may have the same rowid value).
i was trying this quary from past 2 days.. hmm at last found..!! very helpful gud logic..!!!
LikeLike