By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1
(
id int,
foreignId int,
column1 int,
column2 int,
ttimestamp date
);
INSERT INTO table1 VALUES (1,1,1,1,CURDATE());
INSERT INTO table1 VALUES (2,1,1,1,CURDATE()-1);
INSERT INTO table1 VALUES (3,1,1,1,CURDATE()-2);
INSERT INTO table1 VALUES (4,2,2,2,CURDATE());
INSERT INTO table1 VALUES (5,2,2,2,CURDATE()-1);
INSERT INTO table1 VALUES (6,3,2,3,CURDATE());
INSERT INTO table1 VALUES (7,3,3,2,CURDATE());
INSERT INTO table1 VALUES (8,4,4,4,CURDATE());
SELECT * FROM table1;
| id | foreignId | column1 | column2 | ttimestamp |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2022-04-21 |
| 2 | 1 | 1 | 1 | 2022-04-20 |
| 3 | 1 | 1 | 1 | 2022-04-19 |
| 4 | 2 | 2 | 2 | 2022-04-21 |
| 5 | 2 | 2 | 2 | 2022-04-20 |
| 6 | 3 | 2 | 3 | 2022-04-21 |
| 7 | 3 | 3 | 2 | 2022-04-21 |
| 8 | 4 | 4 | 4 | 2022-04-21 |
SELECT x.id FROM (
SELECT id, MAX(foreignId) foreignID, MAX(column1) column1, MAX(column2) column2,
MAX(ttimestamp) column3
FROM table1 GROUP BY foreignId, column1, column2) x;
| id |
|---|
| 1 |
| 4 |
| 6 |
| 7 |
| 8 |
DELETE table1
FROM table1
INNER JOIN (
SELECT
p.id,
ROW_NUMBER() OVER (
PARTITION BY
p.column1,
p.column2,
p.foreignId
ORDER BY
p.ttimestamp DESC
) AS rn
FROM table1 p
) dup
ON table1.id = dup.id
WHERE dup.rn > 1
SELECT * FROM table1;
| id | foreignId | column1 | column2 | ttimestamp |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2022-04-21 |
| 4 | 2 | 2 | 2 | 2022-04-21 |
| 6 | 3 | 2 | 3 | 2022-04-21 |
| 7 | 3 | 3 | 2 | 2022-04-21 |
| 8 | 4 | 4 | 4 | 2022-04-21 |