Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Comparing alternate rows in a query

Posted on 2014-03-13
4
Medium Priority
?
243 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 2000 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

705 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