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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
