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
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 )