Mysql delete duplicate records except one has max time

Question

I have a table which has multiple RFID records and each record has a column called time and what I want is deleting duplicate RFID recods except one which has max time.

table name is attendance_images(id,RFID,time,year,month,day) and my query is as below:

DELETE t FROM attendance_images AS t LEFT JOIN (
   SELECT max( t1.time ) AS time
   FROM attendance_images AS t1
   WHERE t1.year=2016
   AND t1.month=8
   AND t1.day=4
   AND t1.time < 120000
   GROUP BY t1.RFID
) keep ON t.time = keep.time
WHERE keep.time IS NULL
AND t.year =2016
AND t.month =8
AND t.day =4
AND t.time < 120000

The query effect is (0 rows deleted. (Query took 0.0034 sec)) but the table has duplicate records according condition.

Please help to fix this issue.

Answer 1

You need to match on RFID as well:

DELETE t
    FROM attendance_images AS t LEFT JOIN
         (SELECT RFID, max( t1.time ) AS time
          FROM attendance_images AS t1
          WHERE t1.year = 2016 AND t1.month = 8 AND t1.day = 4 AND t1.time < 120000
          GROUP BY t1.RFID
         ) keep
         ON t.time = keep.time AND t.RFID = keep.RFID
WHERE keep.time IS NULL AND
      t.year = 2016 AND t.month = 8 AND t.day = 4 AND t.time < 120000;

Apparently, more than one RFID can have the same maximum time, so joining only on time doesn't work.

EDIT:

You should verify that you actually have data to delete:

select ai.rfid, max(ai.time), min(ai.time), count(*)
from attendance_images ai
where ai.year = 2016 AND ai.month = 8 AND ai.day = 4 AND ai.time < 120000
group by ai.rfid
having min(ai.time) < max(ai.time);

My guess is that this will return no rows, indicating that you have have no such duplicates.

You can change the having clause to having count(*) > 1 to see if you have duplicates that have the same time value.

EDIT II:

The indentation of the values in the query suggest that time is stored as a string, not an integer. You might try replacing the condition:

t.time < 120000

with:

t.time + 0 < 120000