Random record retrieval problem

[leesiulung]

I'm currently using a MS Access database with ColdFusion. The problem is the following query does NOT return random records:

SELECT *
FROM table_name
ORDER BY rnd(integer primary key);

When running this same query in MS Access I get the records returned in random order... In CF there is no error returned, but I always get one of two orders returned. How can I get randomize the order of records retrieved?

Any help would be much appreciated as I struggled with this for some time now.

[Dan_Bracuk]

You could always select your records without an order by clause and use cold fusion functions to re-arrange the records in random order.

[leesiulung]

Dan,

I thought about that, but there could potentially be many records. However, at this point I will even give that a shot. Can you give me some sample code?

btw, I just noticed that there is a database forum here. I will post a link to this thread from there..

[Dan_Bracuk]

The following functions would probably help you in your endeavour.

randrange
valuelist
listlen
listfind
listdeleteat

and maybe these ones also.
querynew
queryaddrow
querysetcell

[paross1]

I have seen this before, I can't remember exactly what the reason for this is, but it has somethingto do with the way that the query is passed to Access via ODBC and when the RND() function is called in the ORDER BY clause, or something like that. Anyway, you might try saving your query within Access itself, then calling that query as a stored procedure from ColdFusion using CFSTOREDPROC and CFPROCRESULT (yes, you can do that). I no longer have any Access DSNs that I can test from ColdFusion, so I can't guarantee that your results will be any different.

Phil

[leesiulung]

paross1,

I tried it with stored procedures and it did not work. I might have to resort to manually doing it in CF code as Dan suggested.

[paross1]

quote:

---

I tried it with stored procedures and it did not work. I might have to resort to manually doing it in CF code as Dan suggested.

---

Didn't work at all, or just didn't order the list randomly?

Phil

[leesiulung]

It did not list the order randomly. However, the stored procedure did fetch a bunch of records. However, calling the function from Access always return the recordset in random order...

[Guest]

I'm not exactly sure I know what you are trying to do or maybe I don't understand
why it is not working.

But it sounds like all you are trying to do is somethinging like this...

SELECT * FROM (
SELECT MyId, MyName, MyEmail, Rnd(MyId) AS MyRnd
FROM myTable
) iTable
ORDER BY MyRnd

Have you tried using a different jdbc driver?

Good luck!

[leesiulung]

That is the gist of it. The problem is that this is through a shared hosting and I cannot install my own drivers. On the other hand, your sql query displayed the random number generated and I noticed that the random numbers never changed...

Turns out chacing is by default on... all I had to do was set the following in the cfquery statment: cachedwithin="#CreateTimeSpan(0,0,0,0)#". This is what I don't like about software that don't document clearly what the default is... according to the livedocs, if cachedwithin is not provided there is no default value. Leads one to incorrectly assume that it is off by default.

Anyhow, I appreciate everyone that participated. Thank you!

[UpstateWeb]

So you got the RND() to work in MS Access. I used this for years succesfully until I upgraded to 6.1 and have never found a solution. Could you better explain what you did. I even tried to use different jdbc drivers but could never get that to work. I would love a solution for getting Random records in a cfquery using MS Access if anyone has one.