How to randomize the order of part of the result set?

Question

I am using ColdFusion 10.

I have a query that I cannot change. I can't change the SQL. The query is being called from several places, including ajax. I can touch only what is outputted from the result set.

I need to randomize the order of new products in the current result set. The new products will always be list first. I will be outputting a maximum of 50 products at one time. There can be as many as 50 new products. So, the result set will never be huge, like a million rows.

Let's just say that my current result set looks like this:

ProductID, IsNew
1          T   
2          T 
3          T 
4          F 
5          F  
6          F

My goal is to randomize the new products and leave the old products in their existing order. So, my new result set might look like this:

ProductID, IsNew
3          T   
1          T 
2          T 
4          F 
5          F  
6          F

My thought is to loop through the results, find the new products (which will always be listed first), add them to a list, randomize the list, then somehow manipulate the result set to use the randomized list. Kind of like this:

// create empty list
NewProductsList = "";
// loop through results and add new products to list
NewProductsList = "1,2,3";
// randomize list of new products
NewProductsList = "3,1,2";

My thought is to use queryAddRow() and querySetCell() functions to rewrite the results. Is there a way to manipulate the order of a result set using ColdFusion upon output?

Answer 1

I'm away from a copy of CF right now, but here's a slightly different implementation to Dan's solution which ought to work, but currently isn't tested.

<cfset QueryAddColumn(myQuery,"sortCol","Decimal")>
<cfloop query="myQuery">
    <cfif myQuery.IsNew>
      <cfset myQuery.sortCol = Rand()> <!--- will be 0-1 --->
    <cfelse>
      <cfset myQuery.sortCol= myQuery.CurrentRow+1> <!--- will always be 2 or greater --->
    </cfif>
</cfloop>

<cfquery name="sorted" dbtype="query">
select * from myQuery order by sortCol
</cfquery>