Mysql query to identify and remove duplicates based on timestamp

Question

I am trying to build a mysql query to list the entire column a that has duplicated column b from the same table. The trick is that I have a timestamp for the rows, so I need to essentially determine which one is older so that I can delete it. Any help would be appreciated.

Answer 1

Just an example - this query returns duplicate messages, now you just need to execute delete

id| title | text_desc | created ------------------------------------------------------- 1 | The title | description here |2012-02-21 10:58:58 2 | The title | description here 1 |2012-02-21 10:58:58 3 | The title | description here 3 |2012-02-21 10:58:58 select bad_rows.* from posts as bad_rows inner join ( select title, MIN(id) as min_id from posts group by title having count(*) > 1 ) as good_rows on good_rows.title = bad_rows.title and good_rows.min_id <> bad_rows.id; 

Here are the return lines

 id| title | text_desc | created ------------------------------------------------------- 2 | The title | description here 1 |2012-02-21 10:58:58 3 | The title | description here 3 |2012-02-21 10:58:58 

Answer 2

Here is your request:

 DELETE FROM tablename WHERE id IN (SELECT t1.id FROM tablename t1 JOIN tablename t2 ON t2.cola = t1.cola AND t2.colb = t1.colb AND t2.timecol > t1.timecol WHERE t1.cola = t1.colb) 

The SELECT statement returns records where cola = colb , and there are other matching rows with a later date. The DELETE statement deletes all records returned by SELECT.

If you want to remove duplicate cola , then this is the request:

 DELETE FROM tablename WHERE id IN (SELECT t1.id FROM tablename t1 JOIN tablename t2 ON t2.cola = t1.cola AND t2.timecol > t1.timecol) 

Answer 3

 SELECT FOOCODE,COUNT(*) AS DUPS FROM TABLE GROUP BY FOOCODE HAVING COUNT(FOOCODE)>1; 

The above query will return u all duplicates. Is this what you are looking for?