Ok I see where you are going with this. your suggested work around does get
the unique values but the output duplicates the same value the number of
time it is referenced in the table. I will play around with it and worse
case I will just use the group field in the cfoutput.
Thanks for the heads up.
Bryan
----- Original Message -----
From: "John" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, May 24, 2001 10:20 PM
Subject: RE: OT DBMS select distinct
> The key word distinct does not work that way. It will only allow
duplicate
> record sets but does nothing for individual fields unless it is a one
field
> query. A work around is as follows but depending on the data set . Hope
> this helps.
>
> John
>
>
> select
> r.ResourceID,
> r.Name,
> r.Description,
> p.ApplicationID
> from
> Resources as r LEFT OUTER JOIN
> Policies as p
> ON
> r.ResourceID = p.ResourceID
> where r.name in (select distinct name from resources)
>
> -----Original Message-----
> From: Bryan LaPlante [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 24, 2001 10:04 PM
> To: CF-Talk
> Subject: OT DBMS select distinct
>
>
> Sorry for the off topic but how do you select distinct only on one of
> several items in a select statement.
> I only want the unique names from the query below but this doesn't seem to
> be working.
>
> Winnt 4 sp6
> CF 4.5.2
> Access or SQL 6.5
>
> select
> r.ResourceID,
> distinct r.Name,
> r.Description,
> p.ApplicationID
> from
> Resources as r LEFT OUTER JOIN
> Policies as p
> ON
> r.ResourceID = p.ResourceID
> The error message
> Error Diagnostic Information
> ODBC Error Code = 37000 (Syntax error or access violation)
>
>
> [Microsoft][ODBC Microsoft Access Driver] Cannot include Memo, OLE, or
> Hyperlink Object when you select unique values (r.Description).
>
> Thanks in advance for your help
>
> Bryan LaPlante
> 816-347-8220
> [EMAIL PROTECTED]
> http://www.netwebapps.com
> Web Development
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
