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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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! --->

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?
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.