Craig Hopson Posted November 3, 2012 Share Posted November 3, 2012 hi guys i have this query SELECT `id`,`uri` FROM `jcow_pages` where `type` = 'u' order by `uri` the problem is it returns 5000+ entrys, I'm looking only for entrys that have the same url (duplicates) but i dont know what the url would be i only want to display url duplicates and i want to be able to do it in phpmyadmin SQL tab Thanks Link to comment Share on other sites More sharing options...
Craig Hopson Posted November 3, 2012 Author Share Posted November 3, 2012 (edited) ok i worked out that this select `id`,`uri` ,count(*) from `jcow_pages` where `type` = 'u' group by `uri` having count(*) > 1 but this only shows what ones are duplicated and how many times not all the id's of each duplicate Edited November 3, 2012 by Craig Hopson Link to comment Share on other sites More sharing options...
niche Posted November 3, 2012 Share Posted November 3, 2012 http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql 1 Link to comment Share on other sites More sharing options...
Craig Hopson Posted November 4, 2012 Author Share Posted November 4, 2012 ok thanks i now have this code SELECT a.id, a.uri, a.typeFROM jcow_pages aINNER JOIN jcow_pages b ON a.uri = b.uriAND a.id < b.idWHERE a.type = 'u'LIMIT 0 , 300 but this is giving me the duplicatse of good entrys any idea why? Link to comment Share on other sites More sharing options...
Craig Hopson Posted November 4, 2012 Author Share Posted November 4, 2012 ok let me give some more information on what i am doing this is the instructions i'm following delete user bugfirst: if you have deleted an user A and another user (or the same user) signs up again with the same username, got to phpMyAdmin --> sql tab and enterSELECT `id`,`uri` FROM `jcow_pages` where `type` = 'u' order by `uri` by default the listing is limited to the first 30 rows, but you need all the rows.if you have the same uri more than one time, remove the ones with the lowest id. ok this would be ok if i didnt have 1000+ entrys so i need the query to show only the lowest id's but not the highest id so i can just delete all this is only a one time problem as i have fixed the bug in the oridgal script Thanks Link to comment Share on other sites More sharing options...
justsomeguy Posted November 5, 2012 Share Posted November 5, 2012 I would write another script to do that. First do a query to get all duplicates, then loop through the duplicates and do another query to get all records with those values and then loop through those and delete everthing except the highest one. If you order in descending order then just skip the first one. It's not the most elegant thing but since it's a one-time script it doesn't really need to be efficient. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now