Geek asks and answers

SQL Remove all duplicates, but the highest from each group for each user ID.

It looks like we could have multiple duplicate values ​​in our preference table , so I want to delete all duplicate entries for each user, but later ones . I spent quite a bit of time trying to come up with a solution, and I just keep going. I tried max, grouped it, subqueries, etc. It is very easy to remove the highest, but not all, but the highest. And especially for every user

So, in the database, I could:

id  :   name:       value   :   userId
1   :   sortOrder:  Asc     :   1
2   :   sortOrder:  Desc    :   1
3   :   sortOrder:  Asc     :   2
4   :   something:  value2  :   1

So, in this case, I want to delete the first row, since it duplicates for user 1 and has the highest identifier and saves all other columns.

So, I know that this is at least a sub-request with a group, but I just can't figure it out. So far I:

SELECT 
    MAX(id),
    name
FROM 
    preference
GROUP BY
    name

which gives me a list of those that I want to keep, BUT , it still skips for each user. So I want to have the opposite a bit if I had this for each user. However, I do not believe that I can use <> in a subquery anyway.

Ultimately, I want to say that delete all those that have a lower id for the same user, which are duplicate.

+5
source share
1 answer

In one case, if the column Idis a unique identifier:

DELETE FROM preference
where Id NOT IN (SELECT MAX(id) FROM preference GROUP BY name, userId)

[: . ]

SELECT * FROM preference
where Id NOT IN (SELECT MAX(id) FROM preference GROUP BY name, userId)

SQLFiddle - . . , ...

+8

More articles:


All Articles