DISTINCT is not a function with parameters. It works on all the selected
data.
Try this:
SELECT DISTINCT email, (
SELECT TOP 1 [name]
FROM table1 AS table1a
WHERE email = table1.email
) AS myName
FROM table1
WHERE status = 'A'
AND email <> ''
> -----Original Message-----
> From: Jim McAtee [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 28 January 2003 10:57 a.m.
> To: CF-Talk
> Subject: OT: Problem with DISTINCT()
>
>
> Any ideas why I might be seeing duplicate email addresses in
> the dataset
> returned by this simple query (Access 97 via ODBC)?
>
> <cfquery name="r" datasource="#dsn#">
> SELECT DISTINCT(email), name
> FROM table1
> WHERE status = 'A'
> AND email <> ''
> </cfquery>
>
> In the output, I'm seeing stuff like:
>
> [EMAIL PROTECTED] Bob Smith
> [EMAIL PROTECTED] B. Smith
>
> The DISTINCT() operates on only the email column in this
> query, correct?
>
> Jim
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
