Ordering lists

[ACS LLC]

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

[Dan_Bracuk]

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.

[ACS LLC]

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

[Dan_Bracuk]

select 1000 NumberToBeChangedLater

from atable

where etc

[ACS LLC]

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_FIELDS CAMP_UID TEMPVALUE 1 4,1 374 1000 2 2,1,4 375 1000 3 1 378 1000 4 1,2,4,5,6 379 1000
CACHED	false
EXECUTIONTIME	0
SQL	SELECT 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_FIELDS CAMP_UID TEMPVALUE 1 4,1 374 3 2 2,1,4 375 5 3 1 378 1 4 1,2,4,5,6 379 9
CACHED	false
EXECUTIONTIME	0
SQL	SELECT 1000 AS TempValue,camp_form_fields,camp_uid FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)

[ACS LLC · Accepted solution]

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>

[ACS LLC]

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_FIELDS COMPUTED_COLUMN_1 1 4,1 1000 2 2,1,4 1000 3 1 1000 4 1,2,4,5,6 1000
CACHED	false
EXECUTIONTIME	0
SQL	SELECT 1000,camp_form_fields FROM campaigns WHERE camp_uid IN (370,372,373,374,375,376,377,378,379)

[Dan_Bracuk]

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.