CODEWITHSUNDEEP
mysqlsql
Pascal
Pascal

Reputation: 2214

Remove all duplicated entries but not the first

I have a table which looks like this:

+ id + lastSeen   + lastSeenMS + value +
| 1  | 1509272314 |     123    |   12  |
| 2  | 1509272314 |     123    |   19  |
| 3  | 1509272314 |     123    |   17  |
| 4  | 1509272532 |     453    |   25  |

Now I need to delete all but the first entry which have the same lastSeen and lastSeenMS value.

I thought about using GROUP BY lastSeen, lastSeenMS but I am not sure how to only delete the last entries and not the first one.

Upvotes: 1

Views: 37

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use:

DELETE FROM table_name
WHERE ID NOT IN (SELECT MIN(ID)
                 FROM table_name
                 GROUP BY lastSeen, lastSeenMS);

Rextester Demo

The record with lowest ID per group will stay in table. Warning! Use this method only when ID is UNIQUE and NOT NULL.

EDIT:

Same logic as above, but applied to MySQL dialect:

DELETE t1
FROM tab t1
LEFT JOIN (SELECT MIN(ID) AS ID
           FROM tab
           GROUP BY lastSeen, lastSeenMS) t2
  ON t1.ID = t2.ID
WHERE t2.ID IS NULL;

Rextester Demo

Upvotes: 4

Related Questions