• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Comparing alternate rows in a query

I have a "find duplicates" query

<CFQUERY Name="GetAppl" Datasource="MyDSN">
  SELECT ApplicantsFile.FName, ApplicantsFile.Area, ApplicantsFile.CREATED, ApplicantsFile.ADDRESS1, ApplicantsFile.DOB, ApplicantsFile.Email
  FROM ApplicantsFile
  WHERE (((ApplicantsFile.TempName) In (SELECT TempName FROM ApplicantsFile As Tmp GROUP BY TempName HAVING Count(*)>1 )))
  ORDER BY ApplicantsFile.TempName

This produces a listing which has each duplicate on alternate rows.

How would I loop through the query and compare the two records on each field looking for similarities?
  • 2
1 Solution
You can reference the record before the current record using this notation...


where colName would be a variable holding the name of the column or you could put in a literal string 'ApplicationsFile'

Using this method you can loop using cfoutput query="GetAppl" and compare the current value with the previous value...

<cfifi GetAppl.applicationsFile is GetAppl['ApplicationsFile'][GetAppl.currentRow-1]>
   they are the same
I'm going to post some links because there are always several takes on this popular subject.
I wouldn't loop (in CF) over the query like you are showing but would find a SQL solution.
I myself might do something like the following:
select a.c1,a.c2.a.c3
from table a
where exists (
select 1 from table b
where b.c1 = a.c1
and b.c2 = a.c2
and b.c3 = a.c3
and b.PK <> a.PK

here are some links for other ideas and explanations:




jdthedjAuthor Commented:
Thanks for the replies gdemaria and dgrafx

Some of the links provided by dgrafx give interesting information, but I am going to accept gdemaria's solution because the "fuzzy logic" idea, although probably the right way to do it is more complex than necessary.  

I need to work in a loop because of all the aberations of spelling in several fields, and these can only be seen by visually scanning the records.  In dumping the whole query I found several "John Smith"'s but most were only duplicated on the name column.  I could ignore those, but there were some that had the same address or email address, but spelled incorrectly in one of the records.
jdthedjAuthor Commented:
Thanks gdemaria!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now