Как удалить все данные из датабазы где значение совпадают, оставить толка одну?

Question

[Lavrov95]

table `cities`

id | name
1  | moscow
2  |  washington
3  |  washington

Answer 1

[Rsa97]

DELETE `c1`.*
  FROM `cities` AS `c1`
  JOIN `cities` AS `c2` ON `c2`.`name` = `c1`.`name`
    AND `c2`.`id` < `c1`.`id`

Answer 2

[Артем]

Как-то так

Answer 3

[profaller]

Например:

DELETE
FROM cities
WHERE id NOT IN (

  SELECT id
  FROM (
         SELECT t1.id

         FROM cities t1

           LEFT JOIN (
                       SELECT id
                       FROM cities
                       GROUP BY name
                     ) t2 ON t1.id = t2.id

         WHERE t2.id IS NOT NULL
       ) tmp
)