mySQL. Delete similar records / rows
I have a simple table as follows:

     id   |   author    |    title    |    CD     |
    ---------------------------------------------------
     1      Lady Gaga    Paparazzi     Full album
     2      Lady Gaga    Alejandro     Full album
     3      Lady Gaga    Alejandro     Single track
     4      Lady Gaga    Fever         Full album
     5      Lady Gaga    Paparazzi     Single track


As you may see there are some duplicates: 'Alejandro' and 'Paparazzi' rows are **similar** but **NOT identical**. I would like to delete the **similar** records and keep only one of the two.

So, for example, I would like to delete all those duplicated records that in the column CD have the **word** 'Single' but keep the similar records that in the column CD have the **word** 'Album' (using **wildcards** and the LIKE statement):

     id   |   author    |    title    |    CD     |
    ---------------------------------------------------
     1      Lady Gaga    Paparazzi     Full album      <- keep
     2      Lady Gaga    Alejandro     Full album      <- keep
     3      Lady Gaga    Alejandro     Single track    <- delete
     4      Lady Gaga    Fever         Full album
     5      Lady Gaga    Paparazzi     Single track    <- delete


I did find a tip about [here][1] but I did not manage to work it out for me.
Thank you.


  [1]: http://blog.krisgielen.be/archives/111
mySQL. Delete similar records / rows
===

I have a simple table as follows:

     id   |   author    |    title    |    CD     |
    ---------------------------------------------------
     1      Lady Gaga    Paparazzi     Full album
     2      Lady Gaga    Alejandro     Full album
     3      Lady Gaga    Alejandro     Single track
     4      Lady Gaga    Fever         Full album
     5      Lady Gaga    Paparazzi     Single track


As you may see there are some duplicates: 'Alejandro' and 'Paparazzi' rows are **similar** but **NOT identical**. I would like to delete the **similar** records and keep only one of the two.

So, for example, I would like to delete all those duplicated records that in the column CD have the **word** 'Single' but keep the similar records that in the column CD have the **word** 'Album' (using **wildcards** and the LIKE statement):

     id   |   author    |    title    |    CD     |
    ---------------------------------------------------
     1      Lady Gaga    Paparazzi     Full album      <- keep
     2      Lady Gaga    Alejandro     Full album      <- keep
     3      Lady Gaga    Alejandro     Single track    <- delete
     4      Lady Gaga    Fever         Full album
     5      Lady Gaga    Paparazzi     Single track    <- delete


I did find a tip about [here][1] but I did not manage to work it out for me.
Thank you.


  [1]: http://blog.krisgielen.be/archives/111