Mysql removes duplicate entries except one has maximum time

Question

I have a table with several RFID records, and each record has a column called time, and I want to delete duplicate RFID codes, except for those with a maximum time.

the table name is visitance_images (id, RFID, time, year, month, day), and my query is as follows:

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

Query effect ( 0 rows deleted. (Query took 0.0034 sec)), but the table has duplicate entries as per the condition.

Help solve this problem.

Answer 1

You also need to map RFID:

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;

-, RFID , time .

EDIT:

, :

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);

, , , .

having having count(*) > 1, , time.

II:

, time , . :

t.time < 120000

:

t.time + 0 < 120000