Thanks Claude, but that doesn't work. (I tried that too) Any duplicates will also be listed in the subquery so they wont appear in the main query.
You have put your finger on the issue - I have tightened up the code that identifies spammers and now it will only add an ip address once, so I'm just tidying it up a bit. I don't suppose it really matters all that much - there's just over 4000 records in the table and there aren't any more duplicates being added (I get about 40-50 a day going in there). But now I have come close to being able to pull out the duplicates but not succeeding, Its like a burr under my saddle - I want to solve the problem now. Cheers Mike Kear Windsor, NSW, Australia 0422 985 585 Adobe Certified Advanced ColdFusion Developer AFP Webworks Pty Ltd http://afpwebworks.com Full Scale ColdFusion hosting from A$15/month - ----Original Message----- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, 13 January 2008 1:22 PM To: SQL Subject: Re: SQL to identify duplicates If the table is not too big, this would do it: SELECT BanID FROM GuestbookBans WHERE AuthorsIP NOT IN (SELECT DISTINCT AuthorsIP FROM GuestbookBans) This will give you all entries appearing more than once. Now if you want to delete all but one, then it's another story ;-) I suppose you need to do this only once, so the simplest would be to do it in a CFOUTPUT loop with a group in BanId. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3017 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
