Topic: MySQL/PHP finding duplicates in table (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=27945" title="Pages that link to Topic: MySQL/PHP finding duplicates in table (Page 1 of 1)" rel="nofollow" >Topic: MySQL/PHP finding duplicates in table <span class="small">(Page 1 of 1)</span>\

 
Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

IP logged posted posted 05-17-2006 14:44 Edit Quote

Greets!

I'm looking for a way to find duplicates in a db table. Let's say I have a zillion records, and want to find any records with duplicate 'name'. How is this accomplished? I know I can do a "select * from myTable where name = 'blah'", but that's manually looking for specific records. I want to spit out all records where a field appears more than once. I hope that makes sense.

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 05-17-2006 15:04 Edit Quote

Couldn't you use GROUP BY to accomplish this?

Use a COUNT to identify multiple occurances, and only use those where count > 1?

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

IP logged posted posted 05-17-2006 18:30 Edit Quote

Indeed, DL is on the right road.

You'll need to use having to select on the grouped columns though...
like

code:
select count (id) as cnt  from myTable GROUP by name HAVING cnt > 1



so long,

->Tyberius Prime

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

IP logged posted posted 05-18-2006 01:10 Edit Quote

Ah - that's cool. Much appreciated!

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

IP logged posted posted 05-18-2006 02:23 Edit Quote

Boy could I have used something like that about 2 months ago.

It sounds so simple when TP says it, but I sure didn't come up with that solution.

- Butcher -

divinechaos
Nervous Wreck (II) Inmate

From:
Insane since: Dec 2001

IP logged posted posted 05-18-2006 08:13 Edit Quote

SELECT DISTINCT might also help compile an index of which rows you want to keep, though admittedly TP hit that nail pretty well already.

Just a thought,
DC



Post Reply
 
Your User Name:
Your Password:
Login Options: Remember Me On This Computer
 
Your Text:
Loading...
Options: Show Signature
Enable Slimies
Enable Linkwords

« BackwardsOnwards »

Show Forum Drop Down Menu