delete duplicates

[siliond]

id tip1 tip2 valoare

15 1 1 23,5
17 1 1 24,5
18 1 1 25
13 2 1 26
14 1 2 27

I have to delete from this table, all the rows that have the same value on (tip1 and tip2), leaving one row. All this with only one delete.

In the example, after the delete query, rows with 17, 18 will be deleted.

Here is a query wich solves the problem:

delete from sterg_daniel
where id <> (select top 1 s.id
from sterg_daniel s
where s.tip1=sterg_daniel.tip1 and s.tip2=sterg_daniel.tip2)
and
(select count(*) from sterg_daniel s2 where sterg_daniel.tip1=s2.tip1 and sterg_daniel.tip2=s2.tip2)>1

I want to optimize this query. Please help me out.

 

[maswien]

delete from sterg_daniel where sterg_daniel.id
not in (select min(id) from sterg_daniel group by tip1, tip2)

 

[siliond]

Your query is slower than previous.
I already find a better solution:

delete from sterg_daniel
where exists (select s1.id from sterg_daniel s1
where s1.tip1=sterg_daniel.tip1 and s1.tip2=sterg_daniel.tip2 and s1.id<sterg_daniel.id
)