Return to Answer

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID FROM TableName GROUP BY uid ) b ON a.uid = b.uid AND a.ID = b.min_ID WHERE b.uid IS NULL 

• SQLFiddle Demo

However, if the records of UID can help different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID, name FROM TableName GROUP BY uid, name ) b ON a.uid = b.uid AND a.ID = b.min_ID AND a.name = b.name WHERE b.uid IS NULL 

• SQLFiddle Demo
• SQLFiddle Demo (the same uid with different name)

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID FROM TableName GROUP BY uid ) b ON a.uid = b.uid AND a.ID = b.min_ID WHERE b.uid IS NULL 

• SQLFiddle Demo

However, if the records of UID can have different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID, name FROM TableName GROUP BY uid, name ) b ON a.uid = b.uid AND a.ID = b.min_ID AND a.name = b.name WHERE b.uid IS NULL 

• SQLFiddle Demo
• SQLFiddle Demo (the same uid with different name)

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID FROM TableName GROUP BY uid ) b ON a.uid = b.uid AND a.ID = b.min_ID WHERE b.uid IS NULL 

• SQLFiddle Demo

However, if the records of UID can help different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID, name FROM TableName GROUP BY uid, name ) b ON a.uid = b.uid AND a.ID = b.min_ID AND a.name = b.name WHERE b.uid IS NULL 

• SQLFiddle Demo

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID FROM TableName GROUP BY uid ) b ON a.uid = b.uid AND a.ID = b.min_ID WHERE b.uid IS NULL 

• SQLFiddle Demo

However, if the records of UID can help different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID, name FROM TableName GROUP BY uid, name ) b ON a.uid = b.uid AND a.ID = b.min_ID AND a.name = b.name WHERE b.uid IS NULL 

• SQLFiddle Demo
• SQLFiddle Demo (the same uid with different name)

One wayt of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID FROM TableName GROUP BY uid ) b ON a.uid = b.uid AND a.ID = b.min_ID WHERE b.uid IS NULL 

• SQLFiddle Demo

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID FROM TableName GROUP BY uid ) b ON  a.uid = b.uid AND a.ID = b.min_ID WHERE b.uid IS NULL 

• SQLFiddle Demo

However, if the records of UID can help different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE a FROM TableName a LEFT JOIN ( SELECT uid, MIN(ID) min_ID, name FROM TableName GROUP BY uid, name ) b ON a.uid = b.uid AND a.ID = b.min_ID AND a.name = b.name WHERE b.uid IS NULL 

• SQLFiddle Demo