coldfusioncoldfusion-9qoq

Sorting in alphabetical order from QoQ result column

I have a result set returned from a SP which is then manipulated in a QoQ. The results are being displayed in ABCabc order. I want them in AaBbCc order.

For example, say the "names" column contains the values "Steve, Andy, anuj, bill, Mike" . When I ORDER BY names, the results are listed in this order:

Instead of the order I want, which is:

Solution

  • A way to work around this is to select the column forcing it to lower or upper and then order by on that column. This example is working for me in CF9 and CF11. Pardon the quick and crude code but just whipped it up to show the ordering working:

    <cfscript>
    qry = QueryNew("TESTING");
    QueryAddRow(qry); QuerySetCell(qry, "TESTING", "A");
    QueryAddRow(qry); QuerySetCell(qry, "TESTING", "B");
    QueryAddRow(qry); QuerySetCell(qry, "TESTING", "C");
    QueryAddRow(qry); QuerySetCell(qry, "TESTING", "a");
    QueryAddRow(qry); QuerySetCell(qry, "TESTING", "b");
    QueryAddRow(qry); QuerySetCell(qry, "TESTING", "c");
</cfscript>

<cfquery name="qry2" dbtype="query">
    SELECT TESTING, LOWER(TESTING) AS TMP FROM qry ORDER BY TMP</cfquery>

<cfoutput query="qry2">
    #TESTING#<br />
</cfoutput>

    That gives me the following output:

    A
a
B
b
C
c

    If you instead were to do an order by without doing lower or upper you end up with output similar to the problem you desribe:

    <cfquery name="qry3" dbtype="query">
    SELECT TESTING, LOWER(TESTING) AS TMP FROM qry ORDER BY TESTING</cfquery>

<cfoutput query="qry3">
    #TESTING#<br />
</cfoutput>

    Output in browser:

    A
B
C
a
b
c