duplicate rows

What is the best way to remove duplicate rows from a table ?Howdy,

Lets assume the Date column in Maintable has duplicates :

select Date
from Maintable
group by Date
having count(Date) > 1

This will select all duplicate dates from the Date column.
Save these dates into a temp table.

Then copy all duplicate rows from the original table into a second temp table. Then add an IDENTITY column to this second temp table.

Now write a routine to delete every second row in the second temp table, using the Identity column to move through the rows...

Then delete ALL duplicate rows from Maintable .

Now insert the rows from the second temp table back into Maintable.

You now have every second ( i.e. no duplicates ) row inserted back into the original table.

Ugly but robust....

Cheers,

SG.sql