Skip to main content
A
ACS LLC
Inspiring
November 4, 2011
Question

SQL Order - matching results to a list

  • November 4, 2011
  • 1 reply
  • 1837 views

I have a list which I query, matchin the list on the camp_uid. The problem is that I want to display the results in the same order as the list was originally compiled, which is not in numeric order. One option is to set up a loop on the original list values, then use a query over query to obtain the results that are appropriate to that camp_uid, but there must be a better way? I search the ORDER command, there does not seem to be anything in place to make it keep the order of the supplied list in the IN statement.

Anybody have any better ideas than my query over query? I am a lilttle concerned over the the performance being forced to run multiple queries over data that I already have, even if they are in a Query over Query

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

    SELECT camp_uid,camp_title,camp_text,camp_image_type

    FROM campaigns

    WHERE camp_uid IN (

<cfqueryparam value="#session.camp_list#" cfsqltype="CF_SQL_INTEGER" list="true">

)

</CFQUERY>

Thanks

Mark

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
November 4, 2011

How was the list originally compiled?

A
ACS LLCAuthor
Inspiring
November 4, 2011

there's a series of different SQL commands that retrieve results based on different criteria, so the list is built from each query, as I went through each one I LISTAPPEND into the list until I had the final list. I have to go back to the page at some point and redisplay in the same order.

I just wrote it with a query that gets all the data, then the loop goes over the session.camp_list and uses a query over query, it works but I'm unsure about performace should it be put under load

D
Dan_Bracuk
Inspiring
November 5, 2011

Create a query object using querynew().  Instead of appending to a list, add rows to this query.  One column will be your value.  The other will be an incrementing number.  Use this incrementing number in the order by clause of your final query of queries.

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