10 Replies Latest reply on Jan 10, 2007 2:32 PM by UpstateWeb

    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.
        • 1. Re: Random record retrieval problem
          Dan Bracuk Level 5
          You could always select your records without an order by clause and use cold fusion functions to re-arrange the records in random order.
          • 2. Re: Random record retrieval problem
            leesiulung Level 1
            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..
            • 3. Re: Random record retrieval problem
              Dan Bracuk Level 5
              The following functions would probably help you in your endeavour.

              randrange
              valuelist
              listlen
              listfind
              listdeleteat

              and maybe these ones also.
              querynew
              queryaddrow
              querysetcell
              • 4. Random record retrieval problem
                paross1 Level 2
                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
                • 5. Re: Random record retrieval problem
                  leesiulung Level 1
                  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.
                  • 6. Re: Random record retrieval problem
                    paross1 Level 2
                    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
                    • 7. Re: Random record retrieval problem
                      leesiulung Level 1
                      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...
                      • 8. Re: Random record retrieval problem
                        <newbie /> Level 1

                        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!



                        • 9. Re: Random record retrieval problem
                          leesiulung Level 1
                          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!

                          • 10. Re: Random record retrieval problem
                            UpstateWeb Level 1
                            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.