Random? It nearly killed him!

[Guest]

Once upon a time when I ran CF ver. 5 and 6, this code worked just fine:

<CFQUERY Name="GetBlogUser" Datasource="#session.DSN1#" Maxrows="1" >
SELECT *
FROM BlogTable
ORDER BY rnd(BlogAuthor)
</CFQUERY>

I'd get a random row returned every time. Life was good.

Once I moved to CF 7 it no longer worked. It instead returned the very same row each time. A cache problem they would tell me so I'd empty the cache and sure enough the next time it actually would be a different order. Next time however it was stuck on the new order.

Recently I have moved to a new server and upgraded my miserable Access table to MySQL. Now, that same code gives me the following:

-------------------------------------------------------------------------------------------------------------------------

General error: FUNCTION kidvoltage.rnd does not exist

The error occurred in C:\Websites\106624de3\TSDRoadRally\index.cfm: line 50

48 :
49 :
50 : <CFQUERY Name="GetEvents" Datasource="#session.DSN1#" MAXROWS="1" cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#">
51 : SELECT *
52 : FROM Events

SQL SELECT * FROM Events WHERE (EventDate >= {d '2007-01-16'} AND EventDate <= {d '2007-01-26'}) ORDER BY rnd(EventDate)
DATASOURCE TSD
VENDORERRORCODE 1305
SQLSTATE S1000

-----------------------------------------------------------------------------------------------------------------------------

So what is the solution that will both satisfy my desire for a random return upon each query as well as satisfy the MySQL beast?

[Guest · Accepted solution]

Ok, I figured it out and it was pretty easy. This works very well:

<CFQUERY Name="GetBlogUser" Datasource="#session.DSN1#" axrows="1" >
SELECT *
FROM BlogTable
ORDER BY RAND() LIMIT 1
</CFQUERY>