How to delete duplicate entries in a table ?

[evk_87]

In oracle 9i ........How to delete duplicate entries in a table ?

if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created

Plz help !!!

[jwenting]

we're not here to do your homework for you, and given your "requirements" it's homework as no real job would restrict you like that.

[like_to_learn]

CREATE TABLE duptest  ( Id varchar(5),  nonid varchar(5));
INSERT INTO duptest VALUES('1','a');
INSERT INTO duptest VALUES('2','b');
DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);

Then you can get the unique records.........................

[alit2002]

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

Merry Christmas
Alistair