Skip to content

Instantly share code, notes, and snippets.

@wwalker
Last active January 3, 2016 23:00
Show Gist options
  • Save wwalker/caaf2070e39d3d409c5e to your computer and use it in GitHub Desktop.
Save wwalker/caaf2070e39d3d409c5e to your computer and use it in GitHub Desktop.
mysql question about duplicates
So, I have a table (simpleified greatly)
CREATE TABLE `participants` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`program_id` int(11) NOT NULL,
`user_key` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
);
I run this:
select count(*) as tot, program_id, user_key from participants group by program_id, user_key having count(*) > 1 order by count(*);
and get 1485 rows in response some with tot as high as 128...
How can I get the id's of the 3579 rows that make up those grouped results?
(prgram_id, user_key should have originally been a unique key; now I need to enforce it so I need to decide which of the duplicates to delete. Having their individual id will allow me to see which one haven't joined ot any other thbles and can therefore just be deleted.)
@wwalker
Copy link
Author

wwalker commented Jan 3, 2016

select p2.id from participants p2 join (select program_id, user_key from participants group by program_id, user_key having count(id) > 1 order by count(id)) p1 on p1.program_id = p2.program_id and p1.user_key = p2.user_key;

Does the trick.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment