0

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:

  • Andy, Mike, Steve, anuj, bill

Instead of the order I want, which is:

  • Andy, anuj, bill, Mike, Steve"
Community
  • 1
  • 1
CFML_Developer
  • 1,565
  • 7
  • 18

1 Answers1

3

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
Snipe656
  • 845
  • 7
  • 15
  • I guess not the best of solutions, but should serve the purpose well. Thanks. – CFML_Developer Apr 09 '15 at 03:21
  • I figured it would be the simplest approach since you stated you are already doing a QoQ. If we could see some of your code it could help in offering what might be the easiest/best approach. You could for example use the Java sort() for this but you would be using that after doing a QoQ which at a glance does not seem all that efficient plus by default that is case sensitive so you would have to deal with that and have the risk of it breaking if you update CF(and in turn Java). – Snipe656 Apr 09 '15 at 14:32