Assuming your PK is named customerID you can do the following: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.customerID in (select min(customerID) from mytable group by email)
There is at least one problem in your query. The in () statement is missing a From statement so that it should read: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email from mytable) But this will not work since all records (even the doublettes) match the criteria. Gert Jeff F schrieb: > 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:312575 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
