7 Replies Latest reply on Sep 25, 2011 7:05 PM by ACS LLC

    Ordering lists

    ACS LLC Level 1

      I have a query that retrieves a UID (camp_uid) and with each camp_uid is field camp_form_fields which contains a numerical list, as an example

       

      camp_uid: 375 , camp_form_fields: 1,4,6,7,8

      camp_uid: 377 , camp_form_fields: 4,7,8

      camp_uid: 390 , camp_form_fields: 9,7,6,3

      camp_uid: 392 , camp_form_fields: 3,4

       

      What I need to do is order the camp_uid into a list, so that the one which has the smallest camp_form_fields is listed first, followed by the next smallest etc, so that the example data above would give the result

       

      camp_list = 392,377,390,375

       

      I guess there is no easy way to get SQL to do it and I've been pondering on how to do this in CF without having too many queries within a loop

       

      Perhaps there is somebody out there that has had to do something similar before? Appreciate any ideas

       

      Thanks

       

      Mark

        • 1. Re: Ordering lists
          Dan Bracuk Level 5

          write your query so that it contains constant integer, such as 1000, in the select clause.  Then loop through the results and change that field based on the the value of camp_list.  Then do a Q of Q with an order by clause.  Your answer will be the valuelist if camp_uid from this last query.

          • 2. Re: Ordering lists
            ACS LLC Level 1

            Thanks again for the assistance

             

            Unfortunately, my SQL skills are not that advanced, to be perfectly honest I'm not quite sure what you mean about the constant integer within the query. I can do Q of Q, so if I saw a rough example of what you are thinking maybe I can figure it out

             

            Thanks

             

            Mark

            • 3. Re: Ordering lists
              Dan Bracuk Level 5

              select 1000 NumberToBeChangedLater

              from atable

              where etc

              • 4. Re: Ordering lists
                ACS LLC Level 1

                OK I wrote this test script, with the results below.. not sure what you mean for the rest of it? I CFLOOP QUERY="queryname" but what am I supposed to do with the results?

                 

                SELECT 1000,camp_form_fields
                FROM campaigns
                WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)

                 

                query
                RESULTSET
                query
                CAMP_FORM_FIELDSCOMPUTED_COLUMN_1
                14,1 1000
                22,1,4 1000
                31 1000
                41,2,4,5,6 1000
                CACHEDfalse
                EXECUTIONTIME0
                SQLSELECT 1000,camp_form_fields FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)
                • 5. Re: Ordering lists
                  Dan Bracuk Level 5

                  First, you have to include the camp_uid in your select clause.  Next, when you are looping through the query, set computed_column_1 to the listlen of camp_form_fields, or whatever criteria you use to determine it's length.

                  • 6. Re: Ordering lists
                    ACS LLC Level 1

                    OK, I changed the results of the query using a loop, by assigning the length ot each list to that number, and then I run a query over that query, somebody showed me how to do that a while ago, but I can't remember how to query over an existing query that is in memory, how do I do that? I know it's simple, when you know how

                     

                    Looks like I am almost there, great solution with little code.

                     

                        SELECT 1000 AS TempValue,camp_form_fields,camp_uid

                        FROM campaigns

                        WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)

                     

                    <CFLOOP QUERY="GetFieldList">

                    <CFSET GetFieldList.TempValue = #Len(GetFieldList.camp_form_fields)#>

                    </CFLOOP>

                     

                    <br>

                     

                    <CFDUMP VAR="#Getfieldlist#">

                     

                    results:

                    query
                    RESULTSET
                    query
                    CAMP_FORM_FIELDSCAMP_UIDTEMPVALUE
                    14,1 374 1000
                    22,1,4 375 1000
                    31 378 1000
                    41,2,4,5,6 379 1000
                    CACHEDfalse
                    EXECUTIONTIME0
                    SQLSELECT 1000 AS TempValue,camp_form_fields,camp_uid FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)

                      

                      

                    query
                    RESULTSET
                    query
                    CAMP_FORM_FIELDSCAMP_UIDTEMPVALUE
                    14,1 374 3
                    22,1,4 375 5
                    31 378 1
                    41,2,4,5,6 379 9
                    CACHEDfalse
                    EXECUTIONTIME0
                    SQLSELECT 1000 AS TempValue,camp_form_fields,camp_uid FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)
                    • 7. Re: Ordering lists
                      ACS LLC Level 1

                      I got it!! That's a great solution, thanks again for all of your help

                       

                      Code:

                      <!--- GET THE LIST --->

                       

                      <CFQUERY name="GetFieldList" DATASOURCE="#datasource#">

                          SELECT 1000 AS TempValue,camp_form_fields,camp_uid

                          FROM campaigns

                          WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)

                      </CFQUERY>

                       

                      <!--- LOOP OVER CHECKING LENGTH OF EACH ITEM IN LIST AND CHANGE THE VALUE IN THE QUERY --->

                      <CFLOOP QUERY="GetFieldList">

                      <CFSET GetFieldList.TempValue = #Len(GetFieldList.camp_form_fields)#>

                      </CFLOOP>

                       

                      <!--- QUERY THE ORIGINAL QUERY AND USE ORDER TO PULL RESULTS BASED ON THE LENGTH --->

                      <CFQUERY dbtype="query" name="SortResults">

                      SELECT *

                      FROM GetFieldList

                      ORDER BY TempValue

                      </CFQUERY>