🚀 go-pugleaf

assuming mytable has a unique primary key on column pk:

set nocount on
while exists (select col1 from mytable group by col1 having count(*)>1)
  begin
	select 	maxpk=max(pk),col1
	into 	#dupes
	from 	mytable
	group by col1
	having count(*)>1
	delete	mytable
	from 	mytable m,
		#dupes d
	where	m.pk=d.maxpk
	drop table #dupes
  end
set nocount off

if mytable doesn't have a unique primary key:

set nocount on
declare	@col1 int
while exists (select col1 from mytable group by col1 having count(*)>1)
  begin
	select 	col1
	into	#dupes
	from 	mytable
	group by col1
	having count(*)>1
	while exists (select * from #dupes) 
	  begin
		set rowcount 1
		select 	@col1=col1
		from 	#dupes
		delete	#dupes		
		delete	mytable
		where	col1=@col1
		set rowcount 0
	  end
	drop table #dupes
  end
set nocount off

hope this helps...
don

Les Gainous wrote:
> 
> I'm using MS-SQL Server 6.5 (but any generic SQL syntax would be
> appreciated):
> 
> How can I delete multiple occurrences of a row within a table. I know
> how to display the rows which are duplicated:
> 
>         select          col1,
>                         count(*)
>         from            myTable
>         group by        col1
>         having          count(*) > 1
> 
> But now that I have that listing, how can I delete the second (or third,
> or...) occurrence of the row--preserving the first occurrence.
> 
> Thanks in advance!
> Les