Removing duplicate rows in Access

Question

I've got a table with duplicates in M$ Access, like:

      NAME 
----------------
Charlie
Anderson Silva
Minotauro
Cigano
Sonnen
Charlie
Charlie
Minotauro
Anderson Silva

How could I delete the duplicates on this table and leave only each unique name?

Something like:

      NAME 
----------------
Charlie
Anderson Silva
Minotauro
Cigano
Sonnen

Is just about using a SELECT inside a a DELETE that Groups By "NAME"? If yes, how would it be? Thanks!

possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — CompuChip, Jan 15 '14 at 12:55

score 1 · Answer 1 · answered Jan 15 '14 at 12:57

1

SELECT
    MIN(ID) AS ID, Name
FROM
    TableName
GROUP BY 
    Name

This will give the smallest ID of each of the names

1 | Charlie   |
2 | Anderson  |
3 | Mino      |

etc

so then you can just

DELETE TableName WHERE ID NOT IN
(
     SELECT
        MIN(ID) AS ID
    FROM
        TableName
    GROUP BY 
        Name
)

answered Jan 15 '14 at 12:57

Squirrel5853

But I don't want with the small names, I want the query to study everything in the row of the collumn – Jan 15 '14 at 13:00
@CharlieVelez then just use what I have done as an example or from the post which was suggested by CompuChip... The main point is you just need to isolate your unique items you wish to keep and then when doing the delete reference the ones you wish to keep therefore deleting everything else. – Squirrel5853 Jan 15 '14 at 15:03

1 Answers1