Search code examples
mysqlduplicatesdelete-row

MySQL Delete duplicates in consecutive rows

Suppose this table:

ID ColA ColB
1   7    8
2   7    9
3   7    9
4   5    8
5   6    9
6   6    9
7   5    4

The PK is the ID coumn. Now, I want to delete all duplicates of ColA and ColB in consecutive rows.

In this example rows 2,3 and 5,6 contain duplicates. These shall be removed so that the higher ID is remained.

The output should be:

ID ColA ColB
1   7    8

3   7    9
4   5    8

6   6    9
7   5    4

How can this be done with mySQL?

Thanks, Juergen

Solution 
  • SELECT 
    ID
FROM
    MyTable m1
WHERE
    0 < (SELECT 
            COUNT(*)
        FROM
            MyTable m2
        WHERE
            m2.ID = m1.ID - 1 AND 
            m2.ColA = m1.ColA AND 
            m2.ColB = m1.ColB)

    and then you can use a

    delete from MyTable where ID in ...

    query. This way it would surely work in any version.