Skip to main content
S
SonOfNels
Known Participant
September 18, 2008
Answered

Random Records Access Database RND() doesn't work

  • September 18, 2008
  • 7 replies
  • 1431 views
I am having a bugger of a time figuring this one out. I will start by trying to explain what I want to do:
Goal: I want to list 12 randomly selected records from a my Access database.
Each time the page is refreshed I want a new set of 12 records listed. I have tried many solutions over the past few days to no avail! Here is what my query looks like:

<CFQUERY NAME="ctlst" DATASOURCE="absi" cachedwithin="#CreateTimeSpan(0,0,0,0)#">
SELECT TOP 12 prodmat.*
FROM Catfit INNER JOIN ProdMat ON Catfit.prodid=ProdMat.prodid WHERE Catfit.slsid=1
ORDER BY Rnd(prodmat.prodid)
</cfquery>

This gets me 12 random records once and then I get the same list over an over again on refresh.
    This topic has been closed for replies.
    Correct answer -__cfSearching__-
    Here is a quick and dirty example. Just set the maximum of rows you want and store them in structure. The reason for using a structure is to eliminate duplicates.

    Then either loop through the structure directly, or convert it to a list.

    7 replies

    -
    -__cfSearching__-
    Inspiring
    September 18, 2008
    I am not certain what you mean. Since I am not familiar with your data, can you give an example of what results are displayed now, versus what you would like displayed?



    S
    SonOfNelsAuthor
    Known Participant
    September 18, 2008
    This is the ouput I have from my query:
    Janome MC9700
    Janome MC11000
    Janome DC3018
    Janome JEM720
    Janome MC3500
    Janome MC6500P
    Janome MC4800QC
    Janome 1600P
    Janome 1600P

    <CFQUERY NAME="rndpick" DATASOURCE="absi">
    SELECT prodmat.*
    FROM Catfit INNER JOIN ProdMat ON Catfit.prodid=ProdMat.prodid
    WHERE catfit.modlid=1
    </cfquery>

    <!--- must handle case when the query contains less than 12 records --->
    <cfset maxRecords = MIN(20, rndpick.recordCount)>
    <cfset recordNumbers = structNew()>
    <cfloop condition="structCount(recordNumbers) lt maxRecords">
    <cfset num = randRange(1, rndpick.recordCount)>
    <cfset recordNumbers
    = num>
    </cfloop>

    <cfoutput>
    <cfloop list="#structKeyList(recordNumbers)#" index="record">
    #rndpick["brand"][record]# #rndpick["modl"][record]#<br>
    </cfloop>
    </cfoutput>

    I have a somewhat normalized database and records with a one to many relationship (I think). I have a product that fits into multiple categories but for this purpose I just want it to show up once. This is very cool by the way!
    -
    -__cfSearching__-
    Inspiring
    September 18, 2008
    SonOfNels wrote:
    > <cfquery name="rndpick" datasource="absi">
    > SELECT CID, prodid
    > FROM catfit
    > WHERE prodid LIKE '4%'
    > </cfquery>

    If the data in the base table changes infrequently, you might consider caching the query. Like you were originally doing.
    -
    -__cfSearching__-Correct answer
    Inspiring
    September 18, 2008
    Here is a quick and dirty example. Just set the maximum of rows you want and store them in structure. The reason for using a structure is to eliminate duplicates.

    Then either loop through the structure directly, or convert it to a list.
    S
    SonOfNelsAuthor
    Known Participant
    September 18, 2008
    That worked just perfect. Now I have one last problem to hurdle and since this is a bit advanced for me how can I make it so there are no duplicates shown from the prodid column?
    S
    SonOfNelsAuthor
    Known Participant
    September 18, 2008
    The code below works with one exception, I don't always get 12 records becuase my ids are not in sequential order.

    <cfquery name="rndpick" datasource="absi">
    SELECT CID, prodid
    FROM catfit
    WHERE prodid LIKE '4%'
    </cfquery>

    <cfoutput query="rndpick" maxrows="12" startrow="#randrange(1,
    rndpick.recordcount)#">
    <table>
    <tr>
    <td>#prodid#</td>
    </tr>
    </table>
    </cfoutput>
    -
    -__cfSearching__-
    Inspiring
    September 18, 2008
    SonOfNels wrote:
    > The code below works

    That depends on the ordering and what is acceptably "random" for your application. I would guess that code displays the records in the sequence, starting from a random point in the query. But again, that depends on ordering.

    > with one exception, I don't always get 12 records
    > becuase my ids are not in sequential order.
    > <cfoutput query="rndpick" maxrows="12" startrow="#randrange(1, rndpick.recordcount)#">

    No. It is because of the start row. If the loop starts at row 80, and your query contains 84 rows, only 5 results will be displayed. Row number 80, 81, 82, 83 and 84. It has nothing to do the ids being non-sequential.

    One option is to use a loop to generate a set of random row numbers, _not_ ids. Then loop through the row numbers and display the query results using array notation: #queryName["column"][rowNumber]#

    <cfoutput>
    <cfloop list="#listOfRandomRowNumbers#" index="row">
    #yourQuery["prodid"][record]# #yourQuery["SomeDescription"][row]#<br>
    </cfloop>
    </cfoutput>

    > for some reason this does not work on version 8. I was using 4.5 and it worked fine.

    CF 4.5 was very different. It was written with C++ and primarily used ODBC. Later versions of ColdFusion are based on java and use jdbc. Aside from the architecture, the database drivers are different. While I doubt there are many differences in how core sql behaves, Access has a fair amount of what you might call non-standard features. So some of them may work differently or may not be supported at all depending on the driver.
    S
    SonOfNelsAuthor
    Known Participant
    September 18, 2008
    Thank you for your awesome explaination of how RandgeRange and startrow work. Forgive me but when you said to generate a list of random rows you went over my head. Can you show me an example of how this would look in code? I have been working on this for 3 days so your help is GREATLY appreciated!
    S
    SonOfNelsAuthor
    Known Participant
    September 18, 2008
    If I take out the cachewithin I still get the same records every time I refresh. I am running Coldfusion server 8 on my dev box and for some reason this does not work on version 8. I was using 4.5 and it worked fine.

    Is there a way to do this using randrange so the numbers it cycles through are the ids from my table instead of sequential?
    D
    Dan_Bracuk
    Inspiring
    September 18, 2008
    You keep getting the same 12 records because you cached your query.
    S
    SonOfNelsAuthor
    Known Participant
    September 18, 2008
    Just a quick note I have tried using RandRange with some success. Th problem with that method is it tries to select records in sequencial order and my records are no longer in order. So for example I may have 1,3,7,8,9,10,15. Now I want it to show 12 records and if the integer is missing from the db I do not get 12 records I get 12 minus the number of integers missing from the db.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices