Randomly sorting Query of Queries
ColdFusion 1054 ViewsThe DB servers all have cool little tricks to randomly sort the records, for example on SQL Server it is simply order by NewID(). Trouble is I had a small table with a very limited number of records in it and needed them randomly sorted for each page view. I didn't think it was something the DB server should have to do.
ColdFusion has Query of Queries that can handle limited SQL and is perfect for diving into cached queries. Great I thought, I'll just use that and add a column to the records then sort from that.
select name,picture, '#rand()#' as sorter
from qry_employees
RESULTS:
Jim Bob 0.313584837792
Jon Paul 0.504286763201
Perfect - now I have a random value in the sorter field and I'll just add order by sorter to the qoq right? Nope. I'm not sure why but while it does change the values it didn't change the sort order. You get the same results everytime. Back to the drawing board.
Let's try adding a empty column to the original cached query, tossing a random number into the column, and then sorting on that column.
<cfquery name="qry_employee" datasource="foo" cachedwithin="#createTimeSpan(1,0,0,0)#">
select name,
picture,
'' as sorter
from employees
</cfquery
<cfloop query="qry_employee">
<cfset querySetCell(qry_employee,"sorter",rand(),currentRow)>
</cfloop>
<cfquery name="qry_employee" dbtype="query">
select name,
picture
from qry_employee
order by sorter
</cfquery>
Success!!! Now we can get a random sort order on cached queries.
I would not recommend this for large datasets without testing. In my case, I only had about 50 records to process. In my limited and very un-scientific tests, I was seeing about 4ms to process the page. I'll note that if I changed from using rand() to createUUID() it went up to about 15ms.
