Remove duplicate from different columns in SQL Server 2000

Question

I have table :

number city1 city2 mentions
1 a b 5
1 b a 5
1 c d 2
1 d c 2

what I need is to remove duplicate records, such as a, b equal to b,a became :

number city1 city2 mentions
1 a b 5
1 c d 2

any clue for me ?

thanks before :)

Answer 1

Like this?

delete from table t1
where exists (
  select *
  from table t2
  where
    t2.number = t1.number and
    t2.city1 = t1.city2 and
    t2.city2 = t1.city1 and
    t2.mentions = t1.mentions and
    t2.city1 < t2.city2
)