MySQL (prior to 4.0) doesn't do subselects (select queries in the WHERE clause). I'm kinda hoping that someone could take that and turn it into something involving joins that would be equivalent and run on MySQL.
There is code given in the MySQL documentation (in the reference section describing the DELETE statement), but from my reading it would delete all rows with duplicates, and you want to keep one. I'm willing to be proved wrong - if I had a copy of MySQL running here I'd build a test table and check.
An alternative might be:
INSERT into temp_table
SELECT MIN(id),all,your,fields,except,id
FROM mytable
GROUP BY all,your,fields,except,id;
DROP TABLE mytable;
ALTER TABLE temp_table RENAME TO mytable;
List all your fields in the same order as they appear in mytable - if id isn't the first field, then you should put the MIN(id) in the appropriate place in the list.
Again, I don't guarantee this will work. I would suggest running it from MySQL directly, rather than via PHP (and do it on a copy first!). I somehow doubt that any indexes on mytable would survive such abuse, so you may want to check the table over afterwards and reconstruct those if necessary. Instead of DROPping mytable and ALTERing the name of temp_table, you could perhaps instead (after filling temp_table):
DELETE FROM mytable;
INSERT INTO mytable SELECT all,your,fields FROM temp_table;
DROP TABLE temp_table;
From PHP, one could go the slow-and-steady route.
Get all of the ids in the table.
SELECT MIN(id) FROM mytable GROUP BY username.
Create a column in the table. Doesn't really matter what type, since it's only temporary - allow it to take on null values and call it "tokeep".
Next a loop: for each id, UPDATE "tokeep" for the record with that id so that it contains a value. Any value will do, so long as it's not null.
After the loop, DELETE FROM mytable WHERE tokeep IS NULL
ALTER TABLE mytable DROP COLUMN tokeep
Now I'm not especially enamoured of this next idea; it seems from my inexperienced perspective like it has the potential to overflow any input buffers MySQL might use, but it can't be broken down into a sequence of statements the way the previous idea does.
With all those minimal IDs SELECTed and gathered, join() them all into a single comma-separated $list, and then
DELETE FROM mytable WHERE id NOT IN ($list)
Whichever way, if you're using the ids as foreign keys anywhere else, then you'll first have to make sure that they're all updated appopriately (if a user has IDs 12 and 57, then all references to user 57 would have to be replaced with references to user 12).
Well, that's just a few ideas. Not having MySQL to play with and try them out, I can only say the usual guff about merchantability and fitness for a particular purpose...
