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?
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>
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.