2

I have an issue where I'm joining two record sets from two different datasources using Ben Nadel's QueryAppend.cfc. The data I get back is correct but the ordering of the data is not expected. The combined result set is being sorted like so with all results starting with a lowercase letter appended to the bottom of the record set:

Screen grab of cfdump

I'm expecting (and need) the following type of sort:

Apple

art

bottle

Boy

Cat

coin

dart

Dog

Code follows:

<!---Calling Template--->

<cfquery name="getDataSet1" datasource="datasource1">
SELECT param1
FROM table1
</cfquery> 

<cfquery name="getDataset2" datasource="datasource2">
 SELECT param1
FROM table2
</cfquery> 

<cfscript>
// Create object
 TheUnionObject = createObject("component", "cfc/QueryAppend");
 // Call the function
myUnionResult = TheUnionObject.QueryAppend(getDataSet1, getDataSet2);
</cfscript> 

<!---Dump results--->
<cfdump var="#myUnionResult#">






 <!---QueryAppend.cfc--->
 <cfcomponent>
 <cffunction name="QueryAppend" access="public" returntype="query" 
 output="false"
 hint="This takes two queries and appends the second one to the first one. 
  Returns the resultant third query.">
 <cfargument name="QueryOne" type="query" required="true" />
 <cfargument name="QueryTwo" type="query" required="true" />
 <cfset var LOCAL = StructNew() />
 <cfquery name="LOCAL.NewQuery" dbtype="query">
       (
            SELECT
               *
            FROM
                ARGUMENTS.QueryOne
        )
    UNION 
        (
            SELECT
                *
            FROM
                ARGUMENTS.QueryTwo
        )  ORDER BY Param1 ASC
  </cfquery>
 <cfreturn LOCAL.NewQuery />
  </cffunction>
  </cfcomponent>

I'm assuming that this default sorting behavior is some under-the-hood ColdFusion code. Can anyone tell me how to effect a change to this default ORDER BY behavior?

cflingo
  • 47
  • 7
  • The queries are sorting correctly in that they are ASCII sorted. I think a quick fix would be to add a field to your select statement that uppercases (`ucase`) the field value you want to sort by and then order by that field, but still output the uncased field. – snackboy Dec 14 '18 at 14:10
  • That was so simple I'm embarrassed I didn't think of it. Thanks for the clue. – cflingo Dec 14 '18 at 14:18
  • Thanks! I'll post (a variation) of my comment as the answer. Cheers! – snackboy Dec 14 '18 at 15:34

1 Answers1

5

A quick fix would be to add a field to your select statement that upper cases (upper) the field value you want to sort by and then order by that field, but still output the uncased field. Something along the lines of: 

select   *, 
         upper(name) as upperName
from     query
order by upperName
SOS
  • 6,430
  • 2
  • 11
  • 29
snackboy
  • 624
  • 3
  • 12