Are you sure that you're really getting duplicates of entirely unique rows? Is this a view you're querying, where you might have one company with multiple phone/email/urls? If you're really getting duplicate distinct rows, that's some odd behavior. If you're getting multiple rows because in each one, there's a column that differs, then that's how distinct works. If you need to display this as one record per company, then just use the group attribute of cfquery:
<cfoutput query="yourquery" group="company"> #company#<br> <cfoutput>#email#</cfoutput> </cfoutput> On 9/11/05, Mark Henderson <[EMAIL PROTECTED]> wrote: > > I have a query thats run from a user search request, that looks like so: > > <cfquery name="searchFarmLinks" dataSource="#request.dsn#"> > SELECT URL, ID, Company, Category, Phone, Email, Paid_listing > FROM nzfarming > WHERE URL IS NOT NULL > AND uCase(Company) like > <cfqueryparam value = "%#uCase(searchtext)#%" > cfsqltype="cf_sql_varchar" /> > ORDER BY Paid_listing ASC, Company ASC; > </cfquery> > > This is fine and works. However, the url is listed (in the output for > display) each time an instance of the search term is matched (obviously) > and I would like to get rid of the duplicates. I tried using DISTINCT > without success > i.e in the query, SELECT UNIQUE URL, Company etc > > If I do a single QoQ, like so > <cfquery name="searchDistinctFarmLinks" dbtype="query"> > SELECT DISTINCT URL > FROM searchFarmLinks > </cfquery> > it DOES appear to return the correct recordset but I'm stumped as to how > to re query based on that result. I also figure there must be a quicker > cleaner way to do this. Any ideas?? All help appreciated. > > TIA > regards > Mark > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217923 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
