Duplicate Records in a table

How do i remove duplicate records from a table with a single query without using cursors or anything like that.

Sample :

tempCol

1

1

2

2

1

P.S The table has only one column

Usually way is: in your query, create a temp table to hold the distinct records from your physical table, then, delete the rows in the physical table, then, insert back from your temp table, finally, drop the temp table.

|||

che3358gives a good way to do it. The only thing I'd add is that you can make it all go faster by doing it only for the duplicate rows, ie, instead of doing it for distinct rows, do a count(*) and a having, ie,

select field1, field2, field3
into #DupTable
from mytable
group by field1, field2, field3
having count(*) > 1

delete mytable
from mytable a, #DupTable b
where a.field1 = a.field1
and b.field2 = b.field2
and ....

insert into mytable (field1, field2, ...)
select field1, field2, ...
from #DupTable