Skip to content
Advertisement

How to query for delete the same data out of the table?

I want the output to look like this.

I want to delete it for the output to look like this. In one group there should be only 1 user_id.

select distinct group_id, user_id, count(*)
from pin_users
where group_id in (select group_id from pin_users)
group by group_id, user_id
having count(*) > 1

I get all user_id, group_id and count more than 1 but I don’t know how to delete duplicates and leave only 1 left.

Ps. My English is probably not perfect, pls excuse any mistakes

Advertisement

Answer

Make a subquery to get a list of minimum ids for any combination of users and groups. Then remove everything else.

DELETE FROM pin_users WHERE id NOT IN (
    SELECT min(id) as id
    FROM pin_users
    GROUP BY group_id, user_id
)
Advertisement