sql-server

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.