How to order a query by a column added using QueryAddColumn()?

[el_sim]

I have a query ("GetTotals"). I'm adding a column there from the other query ("GetTheList") using the standard code:

          <CFSET myArray = ArrayNew(1)>

            <cfloop query="GetTheList">

                <CFSET TotalJobs = GetTheList.Total>

                <CFSET x = ArrayAppend(myArray,TotalJobs)>

            </cfloop>

            <CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>

<CFOUTPUT QUERY="GetTotals"> shows everything the way I need.

Except for one problem: How I order the output by that added column?

Simple re-order (below) works for one column only but not for all of them.

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC

                </cfquery>

All ideas are appreciated!

[duncancumming · Accepted solution]

Not entirely sure what you mean by "works for one column only but not for all of them".  Are you adding multiple columns?  Or is that you're also wanting to do secondary ordering, e.g. have them ordered by Totals then by Name?

So for instance your query is already ordered on Name, you've just added your new Totals column.  You should be able to do:

    <cfquery name="ResortQuery" dbtype="query">

                  SELECT *

                  FROM GetTotals

                  ORDER BY  Totals DESC, Name ASC

      </cfquery>

PS: when you're using <cfset> to call functions like this, you don't have to assign them to a variable:

<CFSET x = ArrayAppend(myArray,TotalJobs)>

<CFSET x = QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>

arrayAppend returns a boolean, queryAddColumn returns an integer indicating the number of the new column.  In most cases these values aren't used; you can just do this:

<CFSET ArrayAppend(myArray,TotalJobs)>

<CFSET QueryAddColumn(GetTotals, "Totals", "VarChar", myArray)>

[el_sim]

Thanks a lot for your reply. Yes, you're right: this code does work as desired. I've fixed my problem - it was outside this piece of code.

Your comment helped to direct my attention to the other places. Thanks again.