How can you delete duplicate rows without using temporary tables ?

Like I have a table stucture

A B C
---------------
1 1 1
1 1 1
1 1 1
1 1 1
2 2 2
2 2 2
2 2 2
2 2 3

I need It like this

A B C
---------------
1 1 1
2 2 2
2 2 3

Sujjest any views you know ...... and thanx in advance

use a cursor

This works for me

Select distinct * into <newtable> from <oldtable>

works like magic??

Look I am not using a temporary table and then to use a new table is all the same . Can u sujjest any other ways ..

Using a cursor is also a very good idea .... But are there any simple solutions like the ans from SPere

And thanx in advance

Sorry about the previous answer:
This is the way it should be done( can be optimized)

declare @lp tinyint -- need for loop
declare @col1 tinyint,@col2 tinyint,@col3 tinyint --3 test columns
set @lp=1 -- like false
while @lp>0
begin
 if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
  set @lp=0 --like true, to end loop
 else
 begin --real brain work here
   select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
  --delete since no primary key,or unique way to identify row
   delete from abc where col1=@col1 and col2=@col2 and col3=@col3
   insert into abc values(@col1,@col2,@col3)
 end
end

If you find another answer would like to hear from you.
Have fun.

Hey this a troubling ans , if the number of columns are more than 40 then .....


:(

Actualy what I meant was if the table structure had columns more than 40 . Are there any general ways to tackle

http://www.humshayarhai.blogspot.com

http://www.seowebtools.blogspot.com

in SQL 2005 you can use the new row_number() function.
this will allow you to identify each row and delete duplicates.
:)

row_number


Joe
http://www.joesdirectory.co.uk

Or, you could just design your table correctly to begin with... Primary keys work wonders ;)

--Jeff Moden

LOL, true, but back to the real world it can still happen...
i had it happen tonight when loading in data from an external CSV data source.
Although the data had unique rows in the CSV file, by the time i had ran my clean up script to clean up the data i was left with some duplicate rows - not all fields were duplicate but enough fields to cause me problems.


Joe
www.joesdirectory.co.uk