Skip to main content
A
ACS LLC
Inspiring
September 25, 2011
Answered

Ordering lists

  • September 25, 2011
  • 1 reply
  • 1154 views

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

This topic has been closed for replies.
Correct answer 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_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)

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>

1 reply

D
Dan_Bracuk
Inspiring
September 25, 2011

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.

A
ACS LLCAuthor
Inspiring
September 26, 2011

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

D
Dan_Bracuk
Inspiring
September 26, 2011

select 1000 NumberToBeChangedLater

from atable

where etc

Firefly Community Gallery
Adobe Firefly

Remix with Firefly Community Gallery

Thousands of free creations to fall in love with and remix in Firefly.

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