user3726494 user3726494 - 4 months ago 12
SQL Question

GROUP BY inverse (mysql)

Is there any way to get the inverse of a group by statement in mysql? My use case is to delete all duplicates.

Say my table looks like this:

ID | columnA | ...
1 | A
2 | A
3 | A
4 | B
5 | B
6 | C


I want my result set to look like this:

ID | columnA | ...
2 | A
3 | A
5 | B


(Essentially this finds all duplicates leaving one behind. Could be used to purge all duplicate records down to 1, or to perform other analysis later).

Answer

One way is to take all but the first id for each value of ColumnA:

select t.*
from t
where t.id > (select min(t2.id) from t t2 where t2.columnA = t.columnA);