CFLib.org – Common Function Library Project

rankScores(query, scoreColumn, rankColumn[, sortOrder])

Last updated July 16, 2013

author

John Ceci

Version: 1 | Requires: CF9 | Library: DataManipulationLib

Description:
Pass in a query with scores and a blank rank column and it will return the query with the rank column populated. When ties are encountered it will assign them the same number and when a non-tie is found it will be incremented based on the number of ties.

Return Values:
Nothing. Updates the query inline

Example:

qBase = queryNew("id,score,rank");
for (i=1; i <= 10; i++){
	queryAddRow(qBase);
	querySetCell(qBase, "id", i);
	querySetCell(qBase, "score", randRange(1,5));
}

qSorted = new Query(sql="SELECT * FROM qBase ORDER BY score", dbtype="query", qBase=qBase).execute().getResult();
writeDump(var=qSorted, label="Before");

rankScores(qSorted, "score", "rank", "ASC");

writeDump(var=qSorted, label="After");

Parameters:

Name Description Required
query A query containing data to rank Yes
scoreColumn The column within the query argument containing the data to rank. Must be ordered according to sortOrder argument Yes
rankColumn The column to put the rankings in Yes
sortOrder One of ASC (default) or DESC No

Full UDF Source:

/**
 * Takes a query and ranks the scores, including ties at the same rank.
 * v0.9 by John Ceci
 * v1.0 by Adam Cameron (improved validation, variable-naming; simplified logic)
 * 
 * @param query 	 A query containing data to rank (Required)
 * @param scoreColumn 	 The column within the query argument containing the data to rank. Must be ordered according to sortOrder argument (Required)
 * @param rankColumn 	 The column to put the rankings in  (Required)
 * @param sortOrder 	 One of ASC (default) or DESC (Optional)
 * @return Nothing. Updates the query inline 
 * @author John Ceci (tigeryan55@gmail.com) 
 * @version 1.0, July 16, 2013 
 */
void function rankScores(required query query, required string scoreColumn, required string rankColumn, sortOrder="ASC"){
	var currentRank		= 0;
	var previousScore	= 0;
	var rankIncrement	= 1;
	var row				= 0;

	if (!(listFindNoCase(query.columnlist, scoreColumn) || listFindNoCase(query.columnlist, rankColumn))) {
		throw(type="InvalidColumnException", message="Invalid score or rank column", detail="One or both of #scoreColumn# or #rankColumn# not found in #query.columnlist#");
	}

	if (!isValid("Regex", sortOrder, "(?i)^(?:ASC|DESC)$" )){
		throw(type="InvalidArgumentException", message="Invalid sortOrder", detail="The sortOrder argument - current value #sortOrder# - must be one of ASC or DESC");
	}

	if (sortOrder == "ASC"){
		previousScore = arrayMin(query[scoreColumn]) - 1;
	}else{
		previousScore = arrayMax(query[scoreColumn]) + 1;
	}

	for (row=1; row <= arrayLen(query[scoreColumn]); row++){
		if (query[scoreColumn][row] == previousScore){
			rankIncrement++;
		}else{
			currentRank += rankIncrement;
			rankIncrement = 1;
		}
		query[rankColumn][row] = currentRank;

		previousScore = query[scoreColumn][row];
	}
}
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