CFLib.org – Common Function Library Project

queryDeDupe(theQuery, keyColumn)

Last updated December 19, 2008

author

Matthew Fusfield

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
This function will find rows with duplicate entries in a given column. Useful for filtering Verity results containing database primary keys in CUSTOM1 or CUSTOM2 fields and anywhere else where duplicate database rows might be present.

Return Values:
Returns a query.

Example:

<cfscript>
Test=QueryNew("key,value");
QueryAddRow(Test);
QuerySetCell(Test,"key",1);
QuerySetCell(Test,"value",'One A');
QueryAddRow(Test);
QuerySetCell(Test,"key",1);
QuerySetCell(Test,"value",'One B');
QueryAddRow(Test);
QuerySetCell(Test,"key",2);
QuerySetCell(Test,"value",'Two');

NewQuery=QueryDeDupe(Test,'key');

</cfscript>

Original query:<BR>
<cfdump var="#Test#">
<BR><BR>
Query with dupes removed:<BR>
<cfset NewQuery=QueryDeDupe(test,'key')>
<cfdump var="#NewQuery#">

Parameters:

Name Description Required
theQuery The query to dedupe. Yes
keyColumn Column name to check for duplicates. Yes

Full UDF Source:

/**
 * Removes duplicate rows from a query based on a key column.
 * Modded by Ray Camden to remove evaluate
 * 
 * @param theQuery 	 The query to dedupe. (Required)
 * @param keyColumn 	 Column name to check for duplicates. (Required)
 * @return Returns a query. 
 * @author Matthew Fusfield (matt@fus.net) 
 * @version 1, December 19, 2008 
 */
function QueryDeDupe(theQuery,keyColumn) {
	var checkList='';
	var newResult=QueryNew(theQuery.ColumnList);
	var keyvalue='';
	var q = 1;
	var x = "";
	
	// loop through each row of the source query
	for (;q LTE theQuery.RecordCount;q=q+1) {

		keyvalue = theQuery[keycolumn][q];
		// see if the primary key value has already been used
		if (NOT ListFind(checkList,keyvalue)) {
			
			/* this is not a duplicate, so add it to the list and copy
			   the row to the destination query */
			checkList=ListAppend(checklist,keyvalue);
			QueryAddRow(NewResult);
			
			// copy all columns from source to destination for this row
			for (x=1;x LTE ListLen(theQuery.ColumnList);x=x+1) {
				QuerySetCell(NewResult,ListGetAt(theQuery.ColumnList,x),theQuery[ListGetAt(theQuery.ColumnList,x)][q]);
			}
		}
	}
	return NewResult;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson