Jacek Szarapa - MS SQL Tips & Tricks - How to remove duplicates leaving only first occurrence

MS SQL Tips & Tricks

How to remove duplicates leaving only first occurrence

This problem is very similar to finding duplicates. We just need to make sure that the only record left will be the first one. That is modification of query from finding duplicates

delete Table 
from (
	select t.Id
	from  Table t
	where exists(
		  Select t2.Id from Table t2
		  where t.Column=t2.Column and
			   t2.Id<t2.Id
	   )
	) as x
where x.Id=Table.Id

It simply finds records for each exists any similar record with smaller ID.

The same situation, if we want only last occurrence to be left.

delete Table 
from (
	select t.Id
	from  Table t
	where exists(
		  Select t2.Id from Table t2
		  where t.Column=t2.Column and
			   t2.Id>t2.Id
	   )
	) as x
where x.Id=Table.Id

See also