Try these. They should work:
SELECT ltrim(rtrim(Email)) AS Email, MIN(My_id) AS My_Id, MIN(MyDisplay) As
MyDisplay,
MIN(MyPassword) as MyPassword
FROM EmailAccts
GROUP BY ltrim(rtrim(Email))
If My_Id is the primary key for the EmailAccts Table:
SELECT ltrim(rtrim(Email)) As Email, My_id, MyDisplay, MyPassword
FROM EmailAccts INNER JOIN (
SELECT MIN(My_Id) as My_Id
GROUP BY ltrim(rtrim(Email))
) tblTemp ON EmailAccts.My_Id = tblTemp.My_Id
-----Original Message-----
From: Tyler Silcox [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 06, 2002 11:13 AM
To: CF-Talk
Subject: Re: distinct query>>>
I just tried this out and it still does the same thing. It looks like it
grabs the distinct emails in the subquery (which is good), but then it
returns 4 rows for that email in the main query if they exists...almost
there. I could rig it up if I wanted to, and just grab the distinct emails,
and then loop another query, but I'd like to do it the proper way if I
can...
Tyler Silcox
email | [EMAIL PROTECTED]
----- Original Message -----
From: "Aaron Rouse" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, February 06, 2002 12:16 PM
Subject: Re: distinct query>>>
I would think it returns all distinct columns that you are selecting. I do
not know exactly what is going on in your needs and wanted results, but
maybe a subquery with an IN statement would be a simple enough way to get
those results. Such as:
SELECT Email, My_id, MyDisplay, MyPassword
FROM EmailAccts
WHERE Email IN (SELECT DISTINCT Email FROM EmailAccts)
Snipe - <CF_BotMaster Network="EFNet" Channel="ColdFusion">
On Wed, 6 Feb 2002, Tyler Silcox wrote:
> I'm trying to pull only DISTINCT emails out of the db, shouldn't this
> do
> it?:
>
> <!--- i select the email accounts --->
> <cfquery name="getEmails" datasource="#MyDatasource#"
> username="#MyUserName#" password="#MyPassword#"> SELECT DISTINCT
> Email, My_id, MyDisplay, MyPassword FROM EmailAccts
> </cfquery>
>
> ..cause it doesn't. This returns every row in the table, and I
> looking f
or
> only unique email addresses, and the data that comes along with it.
>
> Tyler Silcox
> email | [EMAIL PROTECTED]
> website | www.gslsolutions.com
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
