Well I’m sure any SQL developer out there would have encountered a problem where the duplicate records in a table have to eliminated for some reasons.

I recently encountered a similar situation where I had to remove the duplicate user name from a table with just one simple query . Here is how I did it:

Here is my tbl_username table having the following columns UID( auto increment primary key), uname (varchar(100)), fname (varchar(100)):

UID	Uname	fname
1	xyz	xyz
2	xyz	xyz
3	abc	abc
4	qwerty	qwerty
5	qwerty	qwerty

We would have to remove the duplicate user names in order to do that we first build a sub query that picks up the uname and max id of the duplicate records.

Select uname, max(UID) as latestUID from tbl_username group by uname having count(uname) > 1

Result:

Uname	latestUID
xyz	2
qwerty	5

By using the max function we can pick the latest entry to be deleted if you like to pick the lowest use the min() function.

Well if you have got this then its pretty much simple to get the build another sub query on top of this to get the unique IDs and then delete it as usual.

delete from tbl_username
where UID in
(
select latestUID from (
Select uname, max(UID) as latestUID from tbl_username group by uname having count(uname) > 1
)
)

Now the table has the following data in it

UID	Uname	fname
1	xyz	xyz
3	abc	abc
4	qwerty	qwerty

Well this way of deleting would only work if the table uses an integer as a primary key.

This is the cure but prevention of this kind of situation can be resolved by just using unique keys on the Uname field.

Prevention is always better than cure so grab a SQL book if you have done this mistake.