Skip to main content
E
el_sim
Known Participant
July 23, 2014
Answered

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

  • July 23, 2014
  • 1 reply
  • 538 views

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!

This topic has been closed for replies.
Correct answer duncancumming

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)>


1 reply

D
duncancummingCorrect answer
Participating Frequently
July 24, 2014

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)>


E
el_simAuthor
Known Participant
July 24, 2014

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices