Deleting duplicate rows

[Sathyaish]

A very banal, a hackneyed issue, what's the query to delete duplicate rows from a table. For instance if my table has three rows with exactly the same data, then how do I delete two rows and just let one of them remain?

[Lightning]

Do these rows are totally indentical or are is there (1 or more) field that is different (a PK = primary key). If there is no PK then it is not possible (I think) otherwise the query is possible:

select T1.ID , T2.ID from Table T1 where
inner join Table1 T2
T1.Field1 = T2.Field1
T1.Field2 = T2.Field2
T1.Field3 = T2.Field3
T1.ID > T2.ID

[powdir]

don't know what DBMS you are using but for this i think i would:

Create a second table with a schema that matches the first table (the one containing duplicates)

Stick a unique constraint on one of the fields in the SECOND table.

Create a stored proc using the appropriate language - TSQL or PL/SQL or whatever. This proc should populate a cursor with the contents of table 1 and then loop through attempting to insert into table 2.

Catch the inevitable unique constraint exceptions in an error handling block, do nothing and then continue looping by using a resume statement, labels etc.

Cheers...

Just a thought - instead of the above why not just create the second table and populate it by using a SELECT DISTINCT * FROM TABLE_1 ?