Removing duplicate field entries in SQL

Question

Is there anyway I can erase all the duplicate entries from a certain table (users)? Here is a sample of the type of entries I have. I must say the table users consists of 3 fields, ID, user, and pass.

mysql_query("DELETE FROM users WHERE ???") or die(mysql_error());

randomtest
randomtest
randomtest
nextfile
baby
randomtest
dog
anothertest
randomtest
baby
nextfile
dog
anothertest
randomtest
randomtest

I want to be able to find the duplicate entries, and then delete all of the duplicates, and leave one.

Answer 1

You can solve it with only one query.

If your table has the following structure:

CREATE TABLE  `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

you could do something like that (this will delete all duplicate users based on username with and ID greater than the smaller ID for that username):

DELETE users
  FROM users INNER JOIN
  (SELECT MIN(id) as id, username FROM users GROUP BY username) AS t
  ON users.username = t.username AND users.id > t.id

It works and I've already use something similar to delete duplicates.

Answer 2

You can do it with three sqls:

create table tmp as select distinct name from users;
drop table users;
alter table tmp rename users;