SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE measurements ( uid varchar(100), value int, vdate datetime, ts bigint ); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('a', 1, '2024-07-21 12:34:49', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('a', 2, '2024-03-04 19:45:17', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('a', 2, '2024-03-04 19:45:17', unix_timestamp() - 100000); -- dup INSERT INTO measurements (uid, value, vdate, ts) VALUES ('a', 3, '2024-01-28 08:28:38', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('a', 4, '2024-03-04 19:45:17', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('a', 4, '2024-03-04 19:45:17', unix_timestamp() - 100000); -- dup INSERT INTO measurements (uid, value, vdate, ts) VALUES ('b', 54, '2024-03-04 19:45:17', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('b', 54, '2024-03-04 19:45:17', unix_timestamp() - 100000); -- dup INSERT INTO measurements (uid, value, vdate, ts) VALUES ('b', 31, '2024-08-01 12:41:59', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('c', 12, '2024-03-04 19:45:17', unix_timestamp()); INSERT INTO measurements (uid, value, vdate, ts) VALUES ('d', 45, '2024-03-04 19:45:17', unix_timestamp()); DELETE m1 FROM measurements m1, measurements m2 WHERE m1.server_modified_date > m2.server_modified_date AND m1.value = m2.value AND m1.measure_date = m2.measure_date AND m1.uid = 'XXX' AND m2.uid = 'XXX'; SELECT * FROM measurements;