Sort query in custom order

Let's say we have a query of employees with a department column: IT, Marketing, Legal, etc. And we want to sort the query by this column but in a specific order instead of alphabetical order. Eg, we want Legal employees first, then Marketing and then IT. So clearly, we cannot just use "ORDER BY department".

What we can do is to use query of query UNION method. We get all employees that are in Law first, give them an order number of 1, then union with those from Marketing, give them an order number of 2 and so on. Then we order the query by that new order number.

Here is the UDF that I have created for this. First, we expects a number of arguments including the query to be sorted, the name and datatype of the column to sort by, the order to sort and finally the new order number column name (just in case the query already has a column called orderNo):

<!--- Sort a query based on a custom list --->
<cffunction name="QuerySort" displayname="QuerySort" access="public" hint="Sort a query based on a custom list" returntype="query" output="false">
    <cfargument name="query" type="query" required="yes" hint="The query to be sorted">
    <cfargument name="columnName" type="string" required="yes" hint="The name of the column to be sorted">
    <cfargument name="columnType" type="string" required="no" default="numeric" hint="The column type. Possible values: numeric, varchar">
    <cfargument name="orderList" type="string" required="yes" hint="The lsit used to sort the query">
    <cfargument name="orderColumnName" type="string" required="no" default="orderNo" hint="The name of the column containing the order number">
    <cfset var qResult = "">    

    <cfquery name="qResult" dbtype="query">
        <!--- The new query goes here --->
    </cfquery>
 
    <cfreturn qResult>
    
</cffunction>

Then we construct a new query which contains a number of UNION clauses. First, we grab those that are on the order list:

<cfloop from="1" to="#listLen(arguments.orderList)#" index="order">
    SELECT *, #order# AS #arguments.orderColumnName#
    FROM arguments.query
    WHERE #arguments.columnName# = <cfqueryparam value="#listGetAt(arguments.orderList, order)#" cfsqltype="cf_sql_#arguments.columnType#" />
    
    UNION
</cfloop>

Then we grab the remaining:

SELECT *, #listLen(arguments.orderList) + 1# AS #arguments.orderColumnName#
FROM arguments.query
WHERE #arguments.columnName# NOT IN (<cfqueryparam value="#arguments.orderList#" list="yes" cfsqltype="cf_sql_#arguments.columnType#" />)

And finally order by the new column:

ORDER BY #arguments.orderColumnName#

Here is the full UDF:

<!--- Sort a query based on a custom list --->
<cffunction name="QuerySort" displayname="QuerySort" access="public" hint="Sort a query based on a custom list" returntype="query" output="false">
    <cfargument name="query" type="query" required="yes" hint="The query to be sorted">
    <cfargument name="columnName" type="string" required="yes" hint="The name of the column to be sorted">
    <cfargument name="columnType" type="string" required="no" default="numeric" hint="The column type. Possible values: numeric, varchar">
    <cfargument name="orderList" type="string" required="yes" hint="The lsit used to sort the query">
    <cfargument name="orderColumnName" type="string" required="no" default="orderNo" hint="The name of the column containing the order number">
    <cfset var qResult = "">
    
    <!--- Make the order list unique to avoid duplicating query records --->
    <cfset arguments.orderList = ListUnique(arguments.orderList)>
    
    <cfquery name="qResult" dbtype="query">
        <cfloop from="1" to="#listLen(arguments.orderList)#" index="order">
            SELECT *, #order# AS #arguments.orderColumnName#
            FROM arguments.query
            WHERE #arguments.columnName# = <cfqueryparam value="#listGetAt(arguments.orderList, order)#" cfsqltype="cf_sql_#arguments.columnType#" />
           
            UNION
        </cfloop>

        SELECT *, #listLen(arguments.orderList) + 1# AS #arguments.orderColumnName#
        FROM arguments.query
        WHERE #arguments.columnName# NOT IN (<cfqueryparam value="#arguments.orderList#" list="yes" cfsqltype="cf_sql_#arguments.columnType#" />)
       
        ORDER BY #arguments.orderColumnName#
    </cfquery>
    
    <cfreturn qResult>
    
</cffunction>