How do you delete duplicate records in MySQL with certain conditions?

Question

Suppose you have a MySQL table that contains a timestamp column. Is there an efficient query I can run that would eliminate rows where information is identical in the non-timestamp columns except it would return the first and last timestamp for each of these groups?

So for example, running the query on the following table:

|    A    |    B    |  timestamp |
----------------------------------
|    a    |    b    |      1     |
|    a    |    b    |      2     |
|    a    |    b    |      3     |
|    a    |    b    |      4     |
|    a    |    b    |      5     |
|    c    |    d    |      3     |
|    c    |    d    |      4     |
|    c    |    d    |      5     |
|    c    |    d    |      6     |
|    e    |    f    |      1     |
|    e    |    f    |      2     |
|    e    |    f    |      3     |

Would yield:

|    A    |    B    |  timestamp |
----------------------------------
|    a    |    b    |      1     |
|    a    |    b    |      5     |
|    c    |    d    |      3     |
|    c    |    d    |      6     |
|    e    |    f    |      1     |
|    e    |    f    |      3     |

Any help would be greatly appreciated!

Answer 1

You can try the below way -

select * from tablename t
where 
timestamp=(select min(timestamp) from tablename t1 where t.A=t1.A and t.B=t1.B)
or 
timestamp=(select max(timestamp) from tablename t1 where t.A=t1.A and t.B=t1.B)