Solved

Comparing alternate rows in a query

Posted on 2014-03-13
4
229 Views
Last Modified: 2014-03-14
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
Comment
Question by:jdthedj
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 39929009
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
 
LVL 25

Expert Comment

by:dgrafx
ID: 39929053
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
 
LVL 3

Author Comment

by:jdthedj
ID: 39930342
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
 
LVL 3

Author Closing Comment

by:jdthedj
ID: 39930343
Thanks gdemaria!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question