• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • 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
</CFQUERY>

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?
0
jdthedj
Asked:
jdthedj
  • 2
1 Solution
 
gdemariaCommented:
You can reference the record before the current record using this notation...

GetAppl[colName][GetAppl.currentRow-1]

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
</cfif>
0
 
dgrafxCommented:
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:

http://sqlserverplanet.com/tsql/find-duplicate-fields-in-a-table

http://support.microsoft.com/kb/139444

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/58faa89c-eb03-44d1-8888-cc46c376a858/show-only-duplicate-rows

http://stackoverflow.com/questions/16687853/sql-query-to-find-duplicates
0
 
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.
0
 
jdthedjAuthor Commented:
Thanks gdemaria!
0
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

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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