Deleting Duplicates in MySQL

Question

Hello

I have a table like this

userid  visitorid   time
1       10          2009-12-23
1       18          2009-12-06
1       18          2009-12-14
1       18          2009-12-18
1705    1678        2010-01-24
1705    1699        2010-01-24
1705    1700        2010-01-24
1712    1           2010-01-25
1712    640         2010-01-24
1712    925         2010-01-25
1712    1600        2010-01-24
1712    1630        2010-01-25
1712    1630        2010-01-24
1713    1           2010-01-24
1713    1           2010-01-23

I would like to perform a query such that it removes all the duplicates except for the latest one. I Hope you get an idea?

Example, after the query the table must be like this

userid  visitorid   time
1       10          2009-12-23
1       18          2009-12-18
1705    1678        2010-01-24
1705    1699        2010-01-24
1705    1700        2010-01-24
1712    1           2010-01-25
1712    640         2010-01-24
1712    925         2010-01-25
1712    1600        2010-01-24
1712    1630        2010-01-25
1713    1           2010-01-24

Answer 1

Assuming your table is called Visitors:

DELETE v1.* FROM Visitors v1
LEFT JOIN (
    SELECT userid, visitorid, MAX(time) AS time
    FROM Visitors v2
    GROUP BY userid, visitorid
) v3 ON v1.userid=v3.userid AND v1.visitorid=v3.visitorid AND v1.time = v3.time
WHERE v3.userid IS NULL;

Answer 2

DELETE  mo.*
FROM    (
        SELECT  userid, visitorid, MAX(time) AS mtime
        FROM    mytable
        GROUP BY
                userid, visitorid
        ) mi
JOIN    mytable mo
ON      mo.userid = mi.userid
        AND mo.visitorid = mo.visitorid
        AND mo.time < mi.mtime

Answer 3

Delete from YourTable VersionA
  where VersionA.Time NOT IN
    ( select MAX( VersionB.Time ) Time
         from YourTable VersionB
         where VersionA.UserID = VersionB.UserID
           and VersionA.VisitorID = VersionB.VisitorID )

Syntax might need to be adjusted, but SHOULD do the trick. Additionally, you may want to pre-query the Subselect into its own table FIRST, then run the DELETE FROM against that result set.