To view all your records with duplicate email addresses, you might try something like this:
<cfquery name="select_distinct_email" datasource="dsn"> select distinct email from mytable </cfquery> <cfloop query="select_distinct_email"> <cfquery name="get_records_with_duplicate_email" datasource="dsn"> select lastname, firstname, city, state from mytable where email = '#select_distinct_email.email#' </cfquery> <cfoutput query="get_offices_with_duplicate_cities"> #city_state# #office_name#<br> <br> </cfoutput> </cfloop> Check that output and see if that's what you're looking for... Rick > -----Original Message----- > From: Jeff F [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 16, 2008 8:58 AM > To: CF-Talk > Subject: SQL Help > > I've got a table (MySQL) with about 20k records. I'd like to be able to get > all fields from the table > with distinct email addresses. Essentially, I'm weeding out records with > duplicate email addresses. > > What I'm trying does not work: > > SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state > FROM mytable > WHERE mytable.email in (select distinct mytable.email) > > What am I missing?? > > -Johny B > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312576 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4