Jump to content

SQL select where 2 the same


Craig Hopson

Recommended Posts

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

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 by Craig Hopson
Link to comment
Share on other sites

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

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 enter
SELECT `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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...