>- see footer for list info -< Ahhhhhhhhh, good point :OD My minds switched off, maybe a mix of a sub select and a count? Or grouping? Or TOP 1 in the sub select? Or all of them together?
Home time... -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Allan Cliff Sent: 02 February 2006 17:07 To: Coldfusion Development Subject: Re: [CF-Dev] Distinct records in SQL >- see footer for list info -< But if you think about it, the email you are looking for will always be in the distinct list. That line doesn't affect the query. It would be different if you could say WHERE id IN (SELECT DISTINCT Email,id FROM YourTable) But you can't, cos it tries to select the id as distinct too and i'm back where i started Allan ----- Original Message ----- From: Adrian Lynch To: Coldfusion Development Sent: Thursday, February 02, 2006 6:02 PM Subject: RE: [CF-Dev] Distinct records in SQL >- see footer for list info -< DISINCT will act across all the columns you list, so with a primary/unique key in the list of columns, you'll get everything back. Try and sub select: SELECT Email, firstname, surname, telephone, address, postcode, id FROM YourTable WHERE Email IN ( SELECT DISTINCT Email FROM YourTable ) Not pretty, but it should work. Adrian -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Allan Cliff Sent: 02 February 2006 16:50 To: Coldfusion Development Subject: [CF-Dev] Distinct records in SQL >- see footer for list info -< Am having brain failure at the end of today. I have a table with ID, name, address, postcode, email, etc I want to write a query to select DISTINCT emails from the db plus name, postcode and id numbers. Have tried DISTINCT SELECT DISTINCT top 5 Email, firstname, surname, telephone, address, postcode, id but give duplicates Have tried group by but no joy. Please could any kind soul point me in the right direction. (2 queries) Thanks Allan _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
