Select DISTINCT Values using query of queries

I have a CF search function using <cfcollection> which takes a query object as one of the arguments.
Before i run the function, i want to remove all of the duplicates from my query.

but , using  "Select Distinct" in a CF QofQ, I only actually get distinct records when selecting just the one column being filtered.

<cfquery dbtype="Query" name="cleanRecords">
SELECT Distinct body, title, url, description
FROM myQuery

this still returns all records since the other fields are unique

<cfquery dbtype="Query" name="cleanRecords">
SELECT Distinct body
FROM myQuery

this works, but only returns the 'body' column.

What i need to do is get only one of each record where 'body' is unique, ignoring all other columns, but not removing them from the resulting query.

I tried using "group by" in the QofQ, but can only group on the full list of fields being selected or CF chokes.

any suggestions?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

_agx_Connect With a Mentor Commented:
Assuming you don't care which "Title", "URL", etc... are returned for each distinct "Body" value, then you could also try using aggregate functions.  For example something like the query below.   It may not be grouped exactly the way you want, but you get the idea ...

<cfquery name="yourQuery" dbtype="query">
      SELECT  Body, MAX(Title) AS Title, MAX([URL]) AS URL
      FROM    yourQuery
      GROUP BY Body


It's not commonly known, but query of query is actually slower to use than just performing another query against the database.   So, I'm not sure this query will work in query-of-query, but if it doesn't it would be better to just hit the database again instead anyway.

select body,title, url, description
from theTable
where ID in (select min(ID)
     from theTable
     group by body)
MichaelEvangelistaAuthor Commented:
good idea, but...
this isn't coming from a database.

It is a dynamically created query object, built from XML, via CFHTTP...
so I don't really have the option of restricting the original query.
and... i don't have an ID column.

I only have
url, body, title, keywords, description - all text.

The actual query of queries would look like

<cfquery dbtype="query" name="cleanInfo">
select url,body,title,keywords,description
from info
<!--- where something... keeps body unique! --->

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Is the goal to output only the first row of each new body value?

This could easily be done in the output loop just by testing for the change in value of body or by using  cfoutput group="body"

Or do you have to do something beside output the resulting query?
MichaelEvangelistaAuthor Commented:

>> I have a CF search function using <cfcollection> which takes a query object as one of the arguments.

so i need a whole query object i can feed to verity in one bite, nothing i can do from there.

MichaelEvangelistaAuthor Commented:

      SELECT  Body, MAX(Title) AS Title, MAX([URL]) AS URL, MAX(keywords) AS keywords, MAX(description) AS description
      FROM    info
      GROUP BY Body

This gives me unique results only, cutting down 300+ urls to only 125 worth having in our search collection.

as for
>> Assuming you don't care which "Title", "URL", etc... are returned for each distinct "Body" value,

exactly! in this case, if the 'body' is the same, it is the same page of the site being indexed.
(or if multiple pages DO Have the same exact body content, we only need one in our results anyway)

thanks very much.

All Courses

From novice to tech pro — start learning today.