Solved

Comparing alternate rows in a query

Posted on 2014-03-13
4
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
cfchart issue with html 6 179
multi page pdf 4 108
Coldfusion: Two dynamic depending drop down 12 57
Coldfusion UNIX/EPOCH Time Conversion - Time Zone? 11 119
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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