We have answer of your question!

100% solved queries, no empty question

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


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?

Question author Evik-james | Source



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 --->
      <cfset myQuery.sortCol= myQuery.CurrentRow+1> <!--- will always be 2 or greater --->

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

Tickanswer.com is providing the only single recommended solution of the question How to randomize the order of part of the result set? under the categories i.e sql , coldfusion , coldfusion-10 , . Our team of experts filter the best solution for you.

Related Search Queries:

You may also add your answer

Thanks for contributing an answer to Tick Answer!