[SOLVED] Delete partial dulicate rows

Delete partial dulicate rows - sql

I have some troubles with deleting partial duplicate rows

The structure is like this:

+-----+--------+--+-----------+--+------+
| id  | userid |  | location  |  | week |
+-----+--------+--+-----------+--+------+
|   1 |    001 |  | amsterdam |  |   11 |
|   2 |    001 |  | amsterdam |  |   23 |
|   3 |    002 |  | berlin    |  |   28 |
|   4 |    002 |  | berlin    |  |   22 |
|   5 |    003 |  | paris     |  |   19 |
|   6 |    003 |  | paris     |  |   35 |
+-----+--------+--+-----------+--+------+

I only need to keep one row from each userid, it doesn't matter which week number it has.

Thanks, Maxcim

Solution

This should work across most databases:

DELETE
FROM yourTable
WHERE id <> (SELECT MIN(id)
              FROM yourTable t
              WHERE t.userid = userid)

This query would delete from each userid group all records except for the record having the lowest id for that group. I assume that id is a unique column.