you can't really, since adding the ID field will un-distinct two rows.
I'd add a field to the query just as a flag, and then <cfloop> through
the query, and if two entries are the same, set the flag to 1. Then you
can do a query of a query to get the unflagged entries, something like:
<cfquery>
SELECT field1, field2, 0 AS duplicate
</cfquery>
<cfscript>
last1 = "";
last2 = "";
for (i=1;i LTE query.recordcount;i=i+1) {
if (query.field1[i] EQ last1 AND query.field2[i] EQ
last2) {
query.duplicate[i] = 1;
}
last1 = query.field1[i];
last2 = query.field2[i];
}
</cfscript>
<cfquery dbtype = "query">
SELECT *
FROM query
WHERE duplicate = 0
</cfquery>
Not the speediest of processes, but it'll get the job done.
HTH
p.s. <rant>why doesn't cfscript support "i++" and "i .= 1" syntax yet???
argh!!</rant>
-----Original Message-----
From: Joel Blanchette [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 13, 2002 11:45 AM
To: CF-Talk
Subject: SQL Statement question
Hello all,
I have a question about a sql statement I am trying to do.
I am using the select distinct column1, column2 from table
This is working correctly. The problem is when I want to output the
results I need the ID column. How would I do this? I do not want to
distinct that column since some are the same.
Thanks.
Joel
______________________________________________________________________
Why Share?
Dedicated Win 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=coldfusionc
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
