someidが同じで、fooidがXXXから始まるレコードが複数あれば、pkが若いレコード1件だけ残して削除するSQL

delete_duplication.sql

delete from <table> t1
where t1.<someid> is not null
	and t1.<pk> > (
		select min(t2.<pk>) 
		from <table> t2 
		where (t1.<someid> = t2.<someid> or (t1.<someid> is null and t2.<someid> is null))
			and t1.<fooid> like 'XXX%'
		having count(*) > 1
	);